SQL-Lesson 5. Wildcards and regular expressions (LIKE)

Often, to filter data, we will need to sample not by the exact match of the condition, but by an approximate value. That is, when, for example, we search for a product whose name matches a certain pattern (regular expression) or contains certain symbols or words. For such purposes, there is an operator LIKE in SQL, which looks for approximate values. To construct such a pattern, metacharacters (special characters, for searching part of the value) are used, namely: "percent sign" (%) or asterisk (*), "underscore" (_) or "question mark" (?), "square brackets" ([ ]).

1. Metacharacter percent sign (%) or asterisk (*)

Let's, for example, select records from our table that relate only to products that contain the word Skis in their name. To do this, we will create a suitable template:

Run SQLSELECT * 
FROM Sumproduct 
WHERE Product LIKE '*Skis*'

As you can see, the DBMS selected only those records where the Product column contained products containing the word Skis.

This example uses the asterisk (*) metacharacter because MS Access does not support the "percent sign" (%) for the LIKE operator.

2. Metacharacter underscore (_) or question mark (?)

An underscore or question mark is used to replace a single character in a word. Let's replace all the vowels in the word Bikes with a "question mark" (?) and see the result:

Run SQLSELECT * 
FROM Sumproduct 
WHERE Product LIKE 'B?k?s'

We used the "question mark" (?) metacharacter because MS Access does not support the "underscore" (_) for the LIKE operator.

3. Metacharacter square brackets ([ ])

The square bracket metacharacter ([ ]) is used to simultaneously specify a set of characters to search against.

SELECT * 
FROM Sumproduct 
WHERE City LIKE '[TN]*'

In the example above, we selected records where the city names start with the letter T or N in the City field.

The square bracket metacharacter ([ ]) is not supported in the MySQL dialect.

Also, in this case, we can use another metacharacter that does the opposite. Let's add an exclamation mark (!) to our regular expression, which will mean "not equal" (for MS Access) or a power sign (^) (for other DBMS).

SELECT * 
FROM Sumproduct 
WHERE City LIKE '[!TN]*'

That is, the last query we created will be read as: select all columns from the Sumproduct table, and only those records where the names of the cities in the City field do not begin with the letters T or N. Additionally, we note that the set of letters in the "square brackets" metacharacter corresponds only to one position in the text.

We can get a similar result if we use the NOT operator we already know, but with an exclamation point (!) the record will be shorter.