How Do SQL's Greater Than and Less Than Operators Compare Strings?

String Comparison in SQL using Greater Than and Less Than Operators

In SQL, the greater than (>) and less than (<) symbols can be used to compare string values. This functionality is available in both MySQL and SQL Server.

How it Works:

String comparisons using these operators return a boolean value (TRUE, FALSE, or NULL). For the comparison to evaluate to TRUE, the left-hand string must appear before the right-hand string in dictionary order, based on the character set and collation used.

For example, in MySQL's default character set (latin1), the following expression will return TRUE:

'ball' < 'water'

This is because 'b' comes before 'w' in the character set.

Case Sensitivity:

Whether a string comparison is case sensitive or not depends on the database. By default, both MySQL and SQL Server are case insensitive. However, it is possible to make case-sensitive comparisons by specifying a character set collation that is case sensitive. In MySQL, this is by using a character set name that ends in "_cs" instead of "_ci."

For example, in MySQL:

'BALL' < 'water' COLLATE latin1_cs

This expression will return FALSE because 'BALL' and 'water' are considered the same string in a case-sensitive comparison.

Example Queries:

Here is an example query using the greater than operator:

SELECT * FROM table WHERE name > 'John';</p>
<p>This query will return all rows where the value of the 'name' column is greater than 'John' in dictionary order.</p>
<p>Here is an example query using the less than operator:</p>
<pre class="brush:php;toolbar:false">SELECT * FROM table WHERE date_of_birth < '2000-01-01';

This query will return all rows where the value of the 'date_of_birth' column is less than '2000-01-01'.

The above is the detailed content of How Do SQL's Greater Than and Less Than Operators Compare Strings?. For more information, please follow other related articles on the PHP Chinese website!