Comparator operator with string in sql query

Question

What does sql query actually compare when we use comparator with string data type ?

select * from users where name>='abcd';

score 0 · Answer 1 · answered May 09 '19 at 14:04

0

>= compares two strings based on the collation defined for the string.

In general, this means that they are compared alphabetically. So your query would return anything that would follow 'abcd' in the dictionary (plus 'abcd'), including 'abcde' and 'zzz'.

You don't specify the database you are using. Whatever it is, it certainly has documentation on collations and character sets.

answered May 09 '19 at 14:04

Gordon Linoff

1,242,037
58
646
786

suppose we have two users entries one with name = 'ajay' and another with name = '2018'. then what should be the result for query ```select * from users where name>='201899''``` – Ajay May 09 '19 at 14:05
1

That depends on the collation, whether digits are bigger or smaller than lower-case letters, which they are for ASCII character sets. – Gordon Linoff May 09 '19 at 21:54

score 0 · Answer 2 · answered May 09 '19 at 14:05

0

Strings have a natural, lexicographical order. This query will select all the names that come "after" abcd, such as bob (since b is after a) or allen (since l is after b).

answered May 09 '19 at 14:05

Mureinik

297,002
52
306
350

score 0 · Answer 3 · answered May 09 '19 at 14:06

> and < and = means that they are compared alphabetically when that happens, a BOOLEAN value is returned (i.e., a TRUE, FALSE, or NULL).

In your example, the SQL code would return results that were "greater than" or after acbd, so thinks like bbbb or z presumably.

Comparator operator with string in sql query

3 Answers3