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