The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> mySQL BETWEEN, Not inclusive
Dag
post Sep 22 2014, 03:55 AM
Post #1


Member
***

Group: Members
Posts: 89
Joined: 24-October 06
Member No.: 549



Why operator BETWEEN doesn't return the last data group?
CODE

    $sql = "SELECT * FROM $tbl WHERE `data` BETWEEN 'R' and 'Z'";

Z data are not loaded. If I use NOT BETWEEN A and R - then I see them.

Means, first letter IS included and last NOT.

Thanks in advance.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Sep 22 2014, 05:54 AM
Post #2


.
********

Group: WDG Moderators
Posts: 8,043
Joined: 10-August 06
Member No.: 7



Is a DB row called exactly "Z" returned (as opposed to "Zaire", "Zebra", etc)?

User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Dag
post Sep 23 2014, 01:16 PM
Post #3


Member
***

Group: Members
Posts: 89
Joined: 24-October 06
Member No.: 549



Sure Christian.

Zdravko, Zlatko...

Imagine: when I want to see all A - K (including K), I am using
BETWEEN A and L (?!)
and for see all between L and Z (including L), I am using
NOT BETWEEN A and L.
And now I've got what I need.
http://laban.rs/music/performer
or/and
http://laban.rs/music/other

QUOTE(Christian J @ Sep 22 2014, 02:54 PM) *

Is a DB row called exactly "Z" returned (as opposed to "Zaire", "Zebra", etc)?


This post has been edited by Dag: Sep 23 2014, 01:17 PM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
jimlongo
post Sep 24 2014, 09:28 AM
Post #4


This is My Life
*******

Group: Members
Posts: 1,125
Joined: 24-August 06
From: t-dot
Member No.: 16



Normally SQL BETWEEN returns up to Z, but not Z+ is the way I read it.
http://msdn.microsoft.com/en-us/library/ms187922.aspx

>< would return up to just before Z

User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Sep 24 2014, 11:04 AM
Post #5


.
********

Group: WDG Moderators
Posts: 8,043
Joined: 10-August 06
Member No.: 7



QUOTE(Christian J @ Sep 22 2014, 12:54 PM) *

Is a DB row called exactly "Z" returned (as opposed to "Zaire", "Zebra", etc)?

To elaborate on the above speculation, if you do this query:

CODE
SELECT * FROM $tbl WHERE `data` BETWEEN 'R' and 'Z'

on this DB table:

CODE
ID  data
--------
0   Q
1   R
2   Z
3   Za

...then perhaps it will return R and Z, but not Za? So if the table doesn't have any row with the value "Z":

CODE
ID  data
--------
0   Q
1   R
2   Y
3   Za

...then perhaps it will just return R and Y? unsure.gif


User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Sep 24 2014, 11:04 AM
Post #6


.
********

Group: WDG Moderators
Posts: 8,043
Joined: 10-August 06
Member No.: 7



QUOTE(jimlongo @ Sep 24 2014, 04:28 PM) *

Normally SQL BETWEEN returns up to Z, but not Z+ is the way I read it.
http://msdn.microsoft.com/en-us/library/ms187922.aspx

The MySQL manual says something similar:

"If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0."
http://dev.mysql.com/doc/refman/5.7/en/com...perator_between


FWIW http://www.w3schools.com/sql/sql_between.asp says:
"Notice that the BETWEEN operator can produce different result in different databases!
In some databases, BETWEEN selects fields that are between and excluding the test values.
In other databases, BETWEEN selects fields that are between and including the test values.
And in other databases, BETWEEN selects fields between the test values, including the first test value and excluding the last test value.

Therefore: Check how your database treats the BETWEEN operator!"


User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Dag
post Sep 24 2014, 03:00 PM
Post #7


Member
***

Group: Members
Posts: 89
Joined: 24-October 06
Member No.: 549



Thank you guys.

Exactly!
I've added performer with name Z.
http://laban.rs/music/performer%20-%20Copy

Z is not the same as Zd (or even Za). Never thought on that way. So, BETWEEN is not the best way for using that kind of filtering records? What is another solution? Like?

EDIT:
He he smile.gif Zz works. Crazy...

This post has been edited by Dag: Sep 24 2014, 03:10 PM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
jimlongo
post Sep 24 2014, 03:13 PM
Post #8


This is My Life
*******

Group: Members
Posts: 1,125
Joined: 24-August 06
From: t-dot
Member No.: 16



How about adding to the query behind the scenes.

So the front end user wants to search between B and F, you do a query BETWEEN B and G, or in the case of Z Zzzzzzzzzzz like you suggested.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Dag
post Sep 24 2014, 03:18 PM
Post #9


Member
***

Group: Members
Posts: 89
Joined: 24-October 06
Member No.: 549




For B and F, instead the B and G, B and Fz did the same trick. You both helped very much.

What do you think jim, with 2,000 (or 3,000) records, that DB will work fast as do it now?

QUOTE(jimlongo @ Sep 25 2014, 12:13 AM) *

How about adding to the query behind the scenes.

So the front end user wants to search between B and F, you do a query BETWEEN B and G, or in the case of Z Zzzzzzzzzzz like you suggested.

User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
jimlongo
post Sep 24 2014, 03:22 PM
Post #10


This is My Life
*******

Group: Members
Posts: 1,125
Joined: 24-August 06
From: t-dot
Member No.: 16



I'm no dbadmin expert, but I think if it's designed well and mysql is tuned properly it should handle 100s of thousand records without much of a problem.

Many millions you might need SQL Server or Oracle.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Sep 24 2014, 04:21 PM
Post #11


.
********

Group: WDG Moderators
Posts: 8,043
Joined: 10-August 06
Member No.: 7



QUOTE(jimlongo @ Sep 24 2014, 10:13 PM) *

or in the case of Z Zzzzzzzzzzz like you suggested.

What if there's a record named "Zäta" or "Zölle"? Maybe there are even more characters in other alphabets, that I don't know of. unsure.gif

Also, where are special characters sorted in MySQL, like the period sign in "Z. Smith"? (If they are sorted before alphanumeric characters it may not matter in this case.)
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Sep 24 2014, 04:27 PM
Post #12


.
********

Group: WDG Moderators
Posts: 8,043
Joined: 10-August 06
Member No.: 7



QUOTE(Dag @ Sep 24 2014, 10:00 PM) *

What is another solution? Like?

How about just comparing the first character of each DB record?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Dag
post Sep 24 2014, 05:15 PM
Post #13


Member
***

Group: Members
Posts: 89
Joined: 24-October 06
Member No.: 549



Explain. Too much select case or if... making slower everything... I thought between doing that exactly...

Above (re speccharacetrs with diacritical marks)... there are unicode table. If unicode is db code page, then probablysome character above 2000 will do the trick... but again: will it be slow? I'll do some experiments. This is not for chinese people... Russina is about 1200 if I remember well... and, as DB designer, I am using ascii charaters only (in the field name). The second field is original name and I am displaying that one. But working with A-Z. I mean, that is your choice. Everything transliterated.

QUOTE(Christian J @ Sep 25 2014, 01:27 AM) *

QUOTE(Dag @ Sep 24 2014, 10:00 PM) *

What is another solution? Like?

How about just comparing the first character of each DB record?

User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Sep 25 2014, 01:49 AM
Post #14


Jocular coder
********

Group: Members
Posts: 2,298
Joined: 31-August 06
Member No.: 43



QUOTE(Dag @ Sep 25 2014, 07:15 AM) *

Explain. Too much select case or if... making slower everything... I thought between doing that exactly...

Above (re speccharacetrs with diacritical marks)... there are unicode table. If unicode is db code page, then probablysome character above 2000 will do the trick... but again: will it be slow? I'll do some experiments. This is not for chinese people... Russina is about 1200 if I remember well... and, as DB designer, I am using ascii charaters only (in the field name). The second field is original name and I am displaying that one. But working with A-Z. I mean, that is your choice. Everything transliterated.

QUOTE(Christian J @ Sep 25 2014, 01:27 AM) *

QUOTE(Dag @ Sep 24 2014, 10:00 PM) *

What is another solution? Like?

How about just comparing the first character of each DB record?



Things like BETWEEN give me the creeps. Just write the test(s) you mean, and keep it simple.

For K-T: name >= K and name < U
For U-Z: name >=U

Probably you should use this too:
For A-D: name <E

Then no-one will get dropped.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Sep 25 2014, 04:24 AM
Post #15


.
********

Group: WDG Moderators
Posts: 8,043
Joined: 10-August 06
Member No.: 7



QUOTE(Dag @ Sep 25 2014, 12:15 AM) *

Explain. Too much select case or if... making slower everything...

No idea, I'm just a novice in SQL. Could this work:

CODE
SELECT * FROM $tbl WHERE LEFT('data', 1) BETWEEN 'R' and 'Z'

? unsure.gif

http://dev.mysql.com/doc/refman/5.7/en/str...l#function_left

QUOTE
Above (re speccharacetrs with diacritical marks)... there are unicode table.

In Swedish Å Ä and Ö are considered distinct letters (appearing after Z in the alphabet). Alas in the Unicode table it seems Ä comes before Å, i.e. opposite from the Swedish order. Norwegian and Danish use Z Æ Ø Å (where Æ and Ø correspond with Swedish Ä and Ö, but the order is different).

Here are more exceptions: https://en.wikipedia.org/wiki/Alphabetical_...fic_conventions sad.gif
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Dag
post Sep 25 2014, 01:10 PM
Post #16


Member
***

Group: Members
Posts: 89
Joined: 24-October 06
Member No.: 549



CODE
SELECT * FROM $tbl WHERE LEFT('data', 1) BETWEEN 'R' and 'Z'

That should work for sure. But one function (because Left is func.) in addition... I am trying to kip it elementary!!
Small code: filteredRecordsOut('tblPerformer'.'A','L')
and func:
CODE

filteredRecordsOut($tbl,$a,$b) {
$b .= 'zz';
SELECT * FROM $tbl WHERE `data` BETWEEN '$a' and '$b' ORDER BY `data`;
etc...
}

This work fast! Only one problem found in the morning: ZZ Top smile.gif) It was really out of filter. Now iz ok (zz instead of z). Or, u saw experienced jimmy caught that on the fly: he wrote Zzzzzz...

Brian,
"Things like BETWEEN give me the creeps..."
Just add zz after the last letter.

About regional specific sorting. That is pain in the ass. I know that for last 15 years, The base idea is USE ASCII for sorting! And always create 2 fields: ASCII transliteratedNamesField nad originalNamesField. Work always with ASCII field but display names from the original field.

Example: His name is Владимир Ашкенази:
http://laban.rs/music/t?q=p&x=vlas
But his name transliterated is Vladimir Ashkenazy
http://laban.rs/music/q?q=y&x=2010

Russian have GOST; USA have some rule of tranliterating Congress Libraby Titles and Authors or simillar...

Å Ä and Ö and other umlauds and graves...
This Å IS close to A and this Ä too! This Ö is close to O (I mean pronauncing) so translterating can't be wrong.

All above IMHO ofcourse...

This post has been edited by Dag: Sep 25 2014, 01:11 PM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Sep 25 2014, 02:28 PM
Post #17


.
********

Group: WDG Moderators
Posts: 8,043
Joined: 10-August 06
Member No.: 7



QUOTE(Dag @ Sep 25 2014, 08:10 PM) *

Å Ä and Ö and other umlauds and graves...
This Å IS close to A and this Ä too! This Ö is close to O (I mean pronauncing) so translterating can't be wrong.

No, the Swedish(*) letter Å sounds more like the English "Four" (*), while Ä sounds like "Bear", and Ö like "Bird". Some people do use A and O instead of ÅÄÖ when they have to write on foreign keyboards, but that's an emergency solution.

(*) But in Danish the names Aake and Åke should be pronounced the same, AFAIK.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Dag
post Sep 26 2014, 04:14 AM
Post #18


Member
***

Group: Members
Posts: 89
Joined: 24-October 06
Member No.: 549



:: No, the Swedish(*) letter Å sounds more like the English "Four" (*)...

I know I know... worked close with Danish ppl for 10 years... their J (Kaj) iz not dzhej but J (as ij)... sure that pronouncing is not exactly the same... there are ou and au and uu etc... but for visual perception, Aage (pronounced Oge) would have the place in A sorted group. Don't u think so? U disagree with transliterated names sorting?

QUOTE(Christian J @ Sep 25 2014, 11:28 PM) *

QUOTE(Dag @ Sep 25 2014, 08:10 PM) *

Å Ä and Ö and other umlauds and graves...
This Å IS close to A and this Ä too! This Ö is close to O (I mean pronauncing) so translterating can't be wrong.

No, the Swedish(*) letter Å sounds more like the English "Four" (*), while Ä sounds like "Bear", and Ö like "Bird". Some people do use A and O instead of ÅÄÖ when they have to write on foreign keyboards, but that's an emergency solution.

(*) But in Danish the names Aake and Åke should be pronounced the same, AFAIK.

User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Sep 26 2014, 07:17 AM
Post #19


.
********

Group: WDG Moderators
Posts: 8,043
Joined: 10-August 06
Member No.: 7



QUOTE(Dag @ Sep 26 2014, 11:14 AM) *

for visual perception, Aage (pronounced Oge) would have the place in A sorted group. Don't u think so?

I thought so too, but it seems I was wrong:

Correct alphabetization in Danish and Norwegian places Å as the last letter in the alphabet, the sequence being Æ, Ø, Å. This is also true for the alternative spelling "Aa". Unless manually corrected, sorting algorithms of programs localised for Danish or Norwegian will place e.g., Aaron after Zorro. In Danish and Norwegian books, a distinction is made between foreign and local words; thus, for example, the German city Aachen would be listed under A, but the Danish city "Aabenraa" would be listed after Z, Æ and Ø.

In the Swedish alphabet, Å is sorted after Z, as the third letter from the end, the sequence being Å, Ä, Ö.
http://en.wikipedia.org/wiki/Å#Place_in_alphabet


QUOTE
U disagree with transliterated names sorting?

I don't know what to think, actually. blush.gif



User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Dag
post Sep 26 2014, 12:27 PM
Post #20


Member
***

Group: Members
Posts: 89
Joined: 24-October 06
Member No.: 549



Back to subject. We are not talking about local rules but about multilingual / international DB and sorting way. I'll not learn each single language or countri rulez to push something to work on the propper way. A after Z - it's ok if u are working in DK and making their local DB. But if u r doing that what I am doing (and not only me), then each variation of A with any diacritical mark will be before Z smile.gif)
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
2 User(s) are reading this topic (2 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 17th February 2019 - 07:43 AM