How to Compare two Strings in SQL

Problem

You want to to compare two strings alphabetically in SQL.

Solution

The most straightforward method to compare two strings in SQL is to use standard comparison operators (<, >, =, etc.):

SELECT 'Michael' < 'Mike';

Here is the result:

The result of 1 means 'true'. A result of 'false' would show a 0.

We get the result of 'false' if we use the > (greater than) or = (equals) comparison operator. Let's try it:

SELECT 'Michael' > 'Mike';

SELECT 'Michael' = 'Mike';

We get 0 in both cases. As discussed above, this means 'false'.

The string 'Mike' is greater than 'Michael' because of the alphabetical value of its letters: the first letter, M, is the same for both words, as is the second one, i. But the third letter k in 'Mike' has a greater value than c, the third letter in 'Michael'. So, 'Mike' has a greater value overall.

Discussion

Case sensitivity varies by SQL dialect. In MySQL and SQL Server, the comparison is case-insensitive, while in SQLite, PostgreSQL, and Oracle SQL, the comparison is case-sensitive. In a case-insensitive comparison, the case of the character (lowercase or uppercase) does not matter. In a case-sensitive comparison, however, the case of the character matters. 'MIKE' and 'Mike' are treated as different strings if the comparison is case-sensitive, while a case-insensitive comparison treats them as the same.