June 14, 2011 at 12:33 am
Hi,
DECLARE @Name_1 varchar(50)
DECLARE @Name_2 Varchar(50)
SET @Name_1 = 'Shrideshi'
SET @Name_2 = 'CheatExam'
IF @Name_1 < @Name_2
BEGIN
SELECT '"Shirideshi" less than "cheat exam"'
END
ELSE
BEGIN
SELECT ' "shrideshi greater than "cheat exam" '
END
result :
"shrideshi greater than "cheat exam"
can any body explain me , how this out put comes when executing the code?
thanks
June 14, 2011 at 3:39 am
I am not sure but it is comparing single character's ascii value from each variable one by one from lefttoright and when ever it will get greater result it will come out.
so try like this.
DECLARE @Name_1 varchar(50)
DECLARE @Name_2 Varchar(50)
SET @Name_1 = 'cheatexan'
SET @Name_2 = 'CheatExam'
IF @Name_1 < @Name_2
BEGIN
SELECT @Name_1 + ' less than ' + @Name_2
END
ELSE
BEGIN
SELECT @Name_1 + ' greater than ' + @Name_2
END
result will be
cheatexan greater than CheatExam
here n is coming after M so @Name_1 is greather than @Name_2
and If you try with this.
DECLARE @Name_1 varchar(50)
DECLARE @Name_2 Varchar(50)
SET @Name_1 = 'cheatexal'
SET @Name_2 = 'CheatExam'
IF @Name_1 < @Name_2
BEGIN
SELECT @Name_1 + ' less than ' + @Name_2
END
ELSE
BEGIN
SELECT @Name_1 + ' greater than ' + @Name_2
END
give result like this
cheatexal less than CheatExam
here L is coming before M so @Name_1 is less than @Name_2
June 14, 2011 at 5:01 am
Alphabetically 'S' is greater than 'C'
If you think you're comparing the lengths of the strings, you're not. You need to use LEN to see the number of characters or DATALENGTH to see the length of the data (which can be different if you have unicode).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 14, 2011 at 9:21 pm
So... which exam are you cheating on ?? 😛
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 15, 2011 at 7:48 am
String manipulation is a basic and often used part of any programming language including TSQL. I'd recommend reading about collation as well as the String functions.
June 16, 2011 at 3:12 am
😉
June 16, 2011 at 3:13 am
Thanks All..
June 16, 2011 at 3:46 am
To answer why you get the else select statement in the output,whenever you compare strings for less than or greater, the comparison occurs between the ASCII values generated for the string. In your example, the ASCII value for 'Shrideshi' is 83 and ASCII value for 'CheatExam' is 67. The comparison thus becomes if 83 < 67 print statement1 else print statement 2. As the comparison evaluates to false the else part statement gets printed.
June 16, 2011 at 5:29 am
Your code appears to use the ASCII character however, this previous answer looks incorrect. the ascii value for CHEAT = 67 and ascii value for CZZZZZZZZZZ=67
if you do select ascii('Cheat') your result returns only the ascii values for the first character C which is 67
the way i see it is as below
select ascii('C')+ascii('h')+ascii('e')+ascii('a')+ascii('t')
select ascii('c')+ascii('h')+ascii('e')+ascii('a')+ascii('t')
so Cheat is < than cheat
***The first step is always the hardest *******
June 16, 2011 at 8:17 am
It's not a strictly ASCII sort. SQL can and will treat "C" and "c" as equivalents.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 16, 2011 at 1:02 pm
Unless you have case sensitive collation for the database, the first thing SQL Server does on a string compare is convert both sides to upper case, then it compares the 2 strings. The the compare goes left to right converted byte by converted byte.
Take this example:
DECLARE
@S1 VARCHAR(50)
, @S2 varchar(50)
SELECT @S1 = 'aaaa1', @S2 = 'AAAA2'
IF @S1 > @S2
PRINT '@S1 is greater'
ELSE IF @S2 > @S1
PRINT '@S2 is greater'
ELSE
PRINT 'They are equal'
Lower case 'a' has a higher ASCII value than upper case 'A' yet the print will be @S2 is greater. If you change @S1 = 'aaaa2' then they will be equal.
Todd Fifield
June 16, 2011 at 2:14 pm
can you elaborate a bit more please if i search for the ascii value of c and C the values are different
c=99 and C=67
***The first step is always the hardest *******
June 17, 2011 at 8:01 am
glen.wass (6/16/2011)
can you elaborate a bit more please if i search for the ascii value of c and C the values are differentc=99 and C=67
Unless you have case sensitive collation for the database, the first thing SQL Server does on a string compare is convert both sides to upper case, then it compares
I think he covered it pretty well. It does the same thing on a sort. Just accept that c = C, unless you have case-sensitive collation.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 17, 2011 at 8:06 am
Yes i have not noticed that thread until after i had replied 🙂
***The first step is always the hardest *******
June 19, 2011 at 7:53 am
You might as well refer to Grant's answer above as to what the answer in this case can be attributed to.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply