Non-trivial uses of LIKE in SQL

The LIKE operator is generally well-known. Most SQL users know how to find columns that contain a string, start with a string, or end with a string. But some of them, especially beginners, may not find obvious some other interesting uses of LIKE.

Find values that start with a value but contain more characters

Values that start with 'abc':

SELECT * FROM tab
    WHERE col LIKE 'abc_%';

Find values with at least one character

SELECT * FROM tab
    WHERE col LIKE '_%';

Find values with less than three characters

SELECT * FROM tab
    WHERE col NOT LIKE '___%';

Find rows where a column contains another column

SELECT * FROM tab
    WHERE col1 LIKE CONCAT('%', col2, '%');

Find rows where columns start with the same character

SELECT * FROM tab
    WHERE col1 LIKE CONCAT(LEFT(col2, 1), '%');

Any combination of N characters, with a certain length

Find any combination of a, b, and c characters with at least 3 characters. For example, 'abc', 'cab', 'abaca', or 'ccc'.

SELECT * FROM tab
    WHERE
        REPLACE(
            REPLACE(col, 'c', 'a'),
        'b', 'a')
        LIKE '%aaa%'
;

Some databases, like PostgreSQL, support the TRANSLATE() function. This function can be used to make this query much more readable:

SELECT * FROM tab
    WHERE TRANSLATE(col, 'bc', 'aa');

Escape character

Find values containing '%' or '_':

SELECT * FROM tab
    WHERE col LIKE '%\%%' OR col LIKE '\_';

Changing the escape character

But we can also change the escape character. In this example, we’ll use '.' to escape % and _, while \ will be considered as a normal character. We’look for this string: '\%\_'.

SELECT * FROM tab
    WHERE col LIKE '%\.%\._%%'
    ESCAPE '.';