CODEWITHSUNDEEP
mysqlsql
Nav
Nav

Reputation: 10412

where clause in mysql

I have a table named b having a column j which is varchar(3) where I have stored values like 

j    
----
a  
b
c
aa
ab
ac

when I write the statement like this

select * from b where j>"aa";

it shows

j
----
b
c
ab
ac

why is it so ...I thought it would show values upper than 'aa' which should ommit the values 'aa','a','b' and 'c' but it only ommits the value 'a' and 'aa' ...why is it so ? I want to know how it is checks the values when I use multi characters

Upvotes: 0

Views: 200

Answers (6)

hbhakhra
hbhakhra

Reputation: 4236

This works by lexicographical order. Like what you would find in a dictionary.

In a dictionary, first you compare the first letter and then get to the next letter.

Upvotes: 1

anubhava
anubhava

Reputation: 784868

It does so because:

aa comes before b, c, ab, ac but doesn't come before a or aa in alphabetic ordering like English language dictionary.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

It's simple alphabetical order.

SELECT j FROM b ORDER BY j

would return:

j
----
a
aa
ab
ac
b
c

That should then make it clearer that the values > 'aa' are:

SELECT j FROM b WHERE j > 'aa' ORDER BY j


j
----
ab
ac
b
c

Upvotes: 3

egrunin
egrunin

Reputation: 25053

The where clause is comparing using dictionary order:

a aa ab ac b c

Upvotes: 0

Mikhail
Mikhail

Reputation: 9007

This is because the string comparison is done prioritizing the first letter.

If you want to skip all values with string length less than 2, then you need to have 

SELECT * FROM b WHERE LENGTH(j) >= 2 AND j > 'aa'

Upvotes: 0

cdeszaq
cdeszaq

Reputation: 31280

When evaluating the string, b, c, ab and ac are being sorted after aa, and that is why they are being returned. Ultimately, it is due to how the string ordering is done for the table's locale.

Upvotes: 0

Related Questions