SQL Server Performance Forum – Threads Archive

Between X

I have a query which I need to return all the grades from X to X29
When I use Between command it only returns grades X, X1 and X2. But this is working fine for other grades like A B and C it is working —————————————-
http://spaces.msn.com/members/dineshasanka

Dinesh, you’ve made a classical newbie mistake. How should we suggest anything when you don’t provide at least the statement you are using? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Sorry SELECT count(1) FROM RespondentMaster a, CountryMaster b with (NoLock)
where a.CounNo = b.CounNo and a.Status=’Y’ AND a.CounNo = 64
AND (a.Grade IN (‘X’, ‘X0’, ‘X1’, ‘X2’, ‘X3’, ‘X4’, ‘X5’, ‘X6’, ‘X7’, ‘X8’, ‘X9’, ‘X10’,
‘X11’, ‘X12’, ‘X13’, ‘X14’, ‘X15’, ‘X16’, ‘X17’, ‘X18’, ‘X19’, ‘X20’)) Returns 1745 records SELECT count(1) FROM RespondentMaster a, CountryMaster b with (NoLock)
where a.CounNo = b.CounNo and a.Status=’Y’ AND a.CounNo = 64 AND (a.Grade between ‘X’ and ‘X20’ ) Return 992 Records. Fetches only X,X0,X1,X2 Tell me the difference
—————————————-
http://spaces.msn.com/members/dineshasanka

If you’re comparing alphanumerical strings, only the ASCII value of the characters is taken into consideration, not any numerical value that the string is supposed to represent. This means that only grades where the second character’s ASCII value is <= that of the "2" character and the third character’s ASCII value is <= that of the "0" character, are considered to be between "X" and "X20". Just take a better look at your results: * you’re getting "X0", "X1", "X10", "X11" … "X19" and "X20" * you’re filtering out "X3" … "X9" Another classical newbie mistake, I’m afraid.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] [edited for ‘between X and X20’, not ‘between X and X2’]

By the way, to show you understand this well, now you explain to us why "X2A" is being filtered out.[8D] And why "X1000" would not be filtered out.[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

Wish TSQL would have something similar to PHP’s natsort. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

I totaly agreed that between will be better for Numeric.
However, this code was written by some body and I was asked this question. My questionis why this only happening to X grade not for other grades like A,B,Y,Z [B)] —————————————-
http://spaces.msn.com/members/dineshasanka

Are you saying that a query …BETWEEN x1 AND x20 does not return rows like x3 and x4, but a query like BETWEEN a1 AND a20 returns rows like a3 and a4? If so, please provide DDL, sample data and desired output. That really sounds strange. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Yes that is the case. I doubt X is soem key letter. that is why it does not return correctly.
I will give the DDL in later time —————————————-
http://spaces.msn.com/members/dineshasanka

Interesting…[<img src=’/community/emoticons/emotion-1.gif’ alt=’

>>My questionis why this only happening to X grade not for other grades like A,B,Y,Z Are you sure?
Declare @t table(data varchar(10))
insert into @t
select ‘a’ as data union all select ‘a0’ union all select ‘a1’ union all select ‘a2’ union all select ‘a3’
union all select ‘a4’ union all select ‘a5’ union all select ‘a6’ union all select ‘a7’ union all select ‘a8’
union all select ‘a9’ union all select ‘a10’ union all select ‘a11’ union all select ‘a12’ union all select ‘a13’ select * from @t
where data between ‘a1’ and ‘a10’ How many rows do above return? If first letter is fixed, then how abt something like this? select * from @t where substring(data,2,len(data)) between 1 and 10
Madhivanan Failing to plan is Planning to fail

It is clear why x3-x9 are filtered out, but x11-x19 should appear in the result set.

Yes, but if I understood this correct is this behaviour not consistent. a3-a9 are not filtered out, while x3-x9 are. That’s at least what I understood so far and that’s what sounds really strange. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Do the "missing values" actually occur in the column on which you filter? SELECT Grade FROM RespondentMaster GROUP BY Grade

Dinesh, can you highlight the changes you did in your question? Madhivanan Failing to plan is Planning to fail

quote:Originally posted by Madhivanan Dinesh, can you highlight the changes you did in your question?

Some body has put this a sticky note. So I removed it. —————————————-
http://spaces.msn.com/members/dineshasanka

Dinesh, So did you find out the reason? Adriaan

No yet. Just now I started looking at this again. Let you know any findings —————————————-
http://spaces.msn.com/members/dineshasanka

Can any body tell me how Between works? does it converts values to floats —————————————-
http://spaces.msn.com/members/dineshasanka

AFAIK, with character columns, it orders by the corresponding ASCII values for the characters contained in the string. Numbers "0"=48 – "9"=57 Capital letters "A"=65 – "Z"=90 Small letters "a"=97 – "z"=122. Interpunction is all over the range between 0 and 255. A blank space is 32. Diacriticals Ã -Ã¢-Ã© … well, they are all over the place and beyond. Presumably collation (AKA sort order[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]) also has an impact on this. With numerical columns, who knows …

Why do you think it converts to FLOAT?
Would you mind posting your DDL? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

]]>