MySQL string comparison

Compare strings in SQL is one of the most useful and common operations we do when we are building queries. We all have needed sometimes to compare strings in order to filter query results.

In this article, we are going to learn how to work with the two main operators that allow us to make string comparisons in MySQL: LIKE and STRCMP.

First, we have to create a Product table to illustrate our examples. The Product table will have the following structure and content:

1. CREATE TABLE `products` (
2. `id` int(11) NOT NULL,
3. `name` varchar(20) NOT NULL,
4. `description` varchar(50) NOT NULL,
5. `price` float NOT NULL
6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `products` (`id`, `name`, `description`, `price`) VALUES
7. (1, 'Ford Car', 'Fiesta 1.8LT', 15000),
8. (2, 'Chevolet Car', 'Camaro SS', 40000),
9. (3, 'Audi Car', 'A4 Sedan', 30000),
10. (4, 'Volkswagen Car', 'Vento Sedan', 20000),
11. (5, 'Audio Car', 'Pioneer car audio system 250w', 200),
12. (6, 'Audio Speaker', 'Boss car audio speaker 50w', 50),
13. (7, 'BMW Car', 'Serie 2 Coupe', 30000),
14. (8, 'Audi Car', 'A1 Coupe', 20000);
15. ALTER TABLE `products` ADD PRIMARY KEY (`id`);

We can execute the following query to verify the Products table content:

1. SELECT * FROM `products`

Result:

Products table content

MySQL LIKE

The LIKE operator allows us to define a search pattern. The query will return the rows that comply with this pattern.

The search pattern can be exact, or we can use the wildcards “%” and “_”. Wildcard “%” ignores N characters before or after the pattern as we place it. On the other hand, the wildcard “_” ignores a single character.

MySQL LIKE examples

Exact pattern

1. SELECT * FROM `products` a WHERE a.name LIKE 'Audi Car'

Result:

Query result for exact pattern

Wildcard _

Let’s use the ‘_’ wildcard to ignore one character.

1. SELECT * FROM `products` a WHERE a.name LIKE 'Audi_ Car'

Result:

Products that meet the “Audi_” pattern

Wildcard %

Let’s use the ‘%’ wildcard to search for the name of the products that contain the ‘Car’ word.

1. SELECT a.* FROM `products` a WHERE a.name LIKE '%Car%'

Result:

Products with the word ‘Car’ in the name

MySQL STRCMP

MySQL STRCMP allows us to compare two text strings. The function receives 2 parameters that correspond to the two text strings to be compared and has 3 possible results:

-1, if the first text string is shorter than the second.

1. SELECT STRCMP('ab', 'abc');

Result: -1

1, if the first text string is larger than the second.

1. SELECT STRCMP('abc', 'ab');

Result: 1

0, if the two text strings are equal.

1. SELECT STRCMP('abc', 'abc');

Result: 0

How to Use STRCMP() to Compare 2 Strings in MySQL

One of the string functions in MySQL is STRCMP(). This function allows you to compare two strings according to their sort order.

The function accepts two arguments. Each argument is the string to compare. It returns either 1, -1, or 0, depending on whether the first string is larger, smaller, or the same size as the second string, according to sort order.

Syntax

Here’s the syntax:

STRCMP(expr1,expr2)

Where expr1 is the first string and expr2 is the second string.

Examples

Here’s an example where the first string is smaller than the second string according to sort order:

SELECT STRCMP('A', 'B');

Result:

+------------------+
| STRCMP('A', 'B') |
+------------------+
|               -1 |
+------------------+

If we swap the arguments around we get this:

SELECT STRCMP('B', 'A');

Result:

+------------------+
| STRCMP('B', 'A') |
+------------------+
|                1 |
+------------------+

And if we use the same string for both arguments we get this:

SELECT STRCMP('A', 'A');

Result:

+------------------+
| STRCMP('A', 'A') |
+------------------+
|                0 |
+------------------+

Of course, the strings can (and probably will) consist of more than a single character:

SELECT STRCMP('A big box', 'Wind and rain') AS Result;

Result:

+--------+
| Result |
+--------+
|     -1 |
+--------+

Collation

The STRCMP() function uses the collation when comparing the strings. This means that you could get different results depending on the collation being used. The following examples demonstrate this.

Case Insensitive

In this example we perform the comparison using a case insensitive collation (the _ci  part of the collation means case insensitive):

SET @lowercase = _utf8mb4 'a' COLLATE utf8mb4_0900_ai_ci;
SET @uppercase = _utf8mb4 'A' COLLATE utf8mb4_0900_ai_ci;

SELECT STRCMP(@lowercase, @uppercase) AS Result;

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

Case Sensitive

In this example we perform the comparison using a case sensitive collation (the _cs  part of the collation means case sensitive):

SET @lowercase = _utf8mb4 'a' COLLATE utf8mb4_0900_as_cs;
SET @uppercase = _utf8mb4 'A' COLLATE utf8mb4_0900_as_cs;

SELECT STRCMP(@lowercase, @uppercase) AS Result;

Result:

+--------+
| Result |
+--------+
|     -1 |
+--------+

In MySQL, collation can be set at various levels (e.g. connection level, database level, column level, etc) . If you’re not sure what collation is being used, see How to Find the Collation in MySQL.