I dindn't understand how this query works?

Question

I dindn't understand how this query works?

amolsingh9372

SSC Rookie

Points: 34
More actions
October 8, 2013 at 9:47 am

#278720

select * from empolyee where name > 'ax'
IDNAME SALARY DEPT PREVIOUS_SAL
1 amol singh 2000 10 2200
2bill gates 3000 30 3300
3hasan mir 7000 20 7700
4robin 9000 50 9600
how this command compares?

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

amolsingh9372 (10/8/2013)
select * from empolyee where name > 'ax'
IDNAME SALARY DEPT PREVIOUS_SAL
1 amol singh 2000 10 2200
2bill gates 3000 30 3300
3hasan mir 7000 20 7700
4robin 9000 50 9600
how this command compares?

It is using the string value. It is much the same way it orders strings. Anything that sorts before 'ax' or equals 'ax' will not be returned. Anything that sorts after that will be returned. Does that help?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286609 More actions · Answer 2

Please respond to the forum not a private message. That way others who may have the same issue can understand as well.

Think about ordering book titles by name.

Alphabet

Axe Murdering 101

Basketball

What book titles would be after 'ax'? That is what your query is doing.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

amolsingh9372 SSC Rookie Points: 34 More actions · Answer 3

it should show false for alphabet > ax

i applied this logic

first a compares with a then l compares with x

but in my case it is showing true

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

There might be something weird. Maybe the collation used or the way you expect your results.

This returns 'False' for me with the following collation SQL_Latin1_General_CP1_CI_AS

IF 'alphabet' > 'ax'

SELECT 'True'

ELSE

SELECT 'False'

What does the following returns for you?

SELECT DATABASEPROPERTYEX('YourDBName', 'Collation')

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

amolsingh9372 SSC Rookie Points: 34 More actions · Answer 5

ok the ax > alphabet is false ....sorry my mistake

but still robin > ax is returning true

r > a true

but how come o>x ??

Sean Lange SSC Guru Points: 286609 More actions · Answer 6

amolsingh9372 (10/8/2013)
ok the ax > alphabet is false ....sorry my mistake
but still robin > ax is returning true
r > a true
but how come o>x ??

It is the whole string not each character. Go back to my book example.

Would "ax" come after "robin"? Of course not. Try ordering the results but this column. Anything before 'ax' will not be returned, anything after it will be returned.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

amolsingh9372 SSC Rookie Points: 34 More actions · Answer 7

amolsingh9372

SSC Rookie

Points: 34

October 8, 2013 at 1:04 pm

#1656867

ok i got the answer

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

The comparison is not one character by character. Take out a dictionary, find the word axe (starts with ax). Every word after axe is "greater than" and every word before it "less than".

Does this help?