SQL String Comparison, Greater Than and Less Than Operators

SQL: Using = and = to compare string with wildcard

Wildcards are only interpreted when you use LIKE opterator.

So when you are trying to compare against the string, it will be treated literally. So in your comparisons lexicographical order is used.

1) There are no letters before *, so you don't have any rows returned.

2) A is first letter in alphabet, so rest of names are bigger then Abby, only Abby is equal to itself.

3) Opposite of 2)

4) See 1)

5) See 1)

6) This condition is equivalent to Name = 'Abby'.

Is it safe to compare strings with 'greater than' and 'less than' in MySQL?

I think there are some gotchas, you can have a look at documentation here for some details :

http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html

If your fields have null values too, you should also take a look at null-safe comparision operator:
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to

example :

mysql> select "a" > "a ", "A" > "a" , "aB"  > "ab" , "a" >= NULL , "a" <=> NULL ;
+------------+-----------+--------------+-------------+--------------+
| "a" > "a " | "A" > "a" | "aB"  > "ab" | "a" >= NULL | "a" <=> NULL |
+------------+-----------+--------------+-------------+--------------+
|          0 |         0 |            0 |        NULL |            0 |
+------------+-----------+--------------+-------------+--------------+

String greater than or less than

It works as it should. To compare the numeric value, you'll have to convert these to numbers somehow. A good start would be to use substr(yourfieldname, 3) to cut of the/a. Then you can useconvert` to typecast it to int, so your final query will look something like:

select * from sequences where convert(int, substr(nbr, 3)) <= 10

Mind that the exact functions and rules for converting strings to ints may very per dbms. This illustrates the general idea, though.

SQL uses of less than or equal to = vs. not greater than ! operators

<= and > are comparison operators, not logical operators. ! is a logical operator (means NOT). When you combine ! and >, you're simply inverting a comparison operator, so your end result is the same.

Having said that, <= is the common form, so I'd say it's preferred, for readability if nothing else. I don't know if there's a performance benefit to either, but I doubt it.

Edit:
Also, you didn't say which flavor of SQL you're dealing with. As @harryovers pointed out, that's a valid operator in MS-SQL, but it might not work everywhere.

---

Related Topics