How do we search for patterns containing the actual characters "%" or "_"?

Question

When using SQL LIKE operators, how do we search for patterns containing the actual characters “%” or “_”?

Answer

When searching for a pattern containing the specific characters % or _, we can utilize the escape character \, similarly to its use in Python.

If we want to search for these specific characters, we can simply add the escape character immediately before them.

Example

/* 
In this pattern, we use an escape character before '%'.
This will only match "%" and not be used like the
wildcard character.

This query will match any titles that end with
' 100%'.
*/

SELECT *
FROM books
WHERE title LIKE '% 100\%';
26 Likes

It should be mentioned that the escape character is a backslash. As much as http protocol has reinforced “http, colon, forward-slash, forward-slash,” I still sometimes find myself forgetting which one is forward and which is back.

/ (forward)
\ (back)

56 Likes

Thank you. It’s quite useful

So, if its need to be found phrase “Anna\Hanna” the query needs to be:

SELECT *
FROM books
WHERE title LIKE 'Anna\\Hanna';

?

8 Likes

I would write something like

SELECT *
FROM books
WHERE tittle LIKE ‘%nna’

I think the question here was, if we need to search for a title with “” character in it, how to we do that?

I’ve found it is best to think of the slash as a man. Is he leaning forward /, or backwards ? Hope this helps someone remember it easier!

9 Likes

Yes, thank you.

Another easy way to remember which is back or forward is:

Forward slash leans or bends forward /

while

Backslash leans or bends backward \

:slight_smile:

6 Likes

Western writings move from left to right, so if the SLASH is leaning towards the right, it is leaning forward. But if you’re used to eastern writings which mostly start from right to left (except text from Red China which apes the West), then life in the SQL world becomes seriously messed up!

3 Likes

You are correct. To search for “Anna\Hanna” you need to specify ‘Anna\\Hanna’

1 Like

ivanvoitenko already assumed that in order to filter names that contain a single blackslash \ the operator has to be:
LIKE ’ % \ \ % ’
which raises another question:
If the title contains 2 backslashes \ \ next to each other, does the operator have to be:
LIKE ’ % \ \ \ \ % ’
or
LIKE ’ % \ \ \ % ’
?
(ignore the spaces within the ’ ’ I added them because the textconverter in this forum messed up my message without these spaces)

1 Like

why ‘The%’ (without space) is not working?

1 Like

@fedehk
if you write “the%” without a space inbetween, what you are looking for is “the” followed by any number of characters which could be zero characters.
In “The Last Dance” , “the” is followed by zero characters so it matches
In “There is something about you” , “the” is followed by 2 characters so it also matches the given pattern.
so "they, “them”, “then” will also match

However, when we write “the %”. It means we are matching only movies that starts with “the”, followed by a "space"and then any number of characters.
In the exercise we only wanted films with the word “the”.

1 Like

Going up / forward backslash.
Going down \ backward backslash.

Thats the idea that came to my mind alone. As I read afterwards… kosiok already wrote it…more or less as my idea. :slight_smile:

1 Like

@digital8761097125 I don’t believe so. I was testing this in SQLite just now and I don’t think backslash is one the requires escape character but I could be wrong.

I generated a table with a variable “name” and a couple of rows containing backslash in the name string. I simply used

WHERE name LIKE “%%”;

and it correctly identifies the rows for me. Test this for yourself but this lead me to think the escape backslash really only needed to be used on the wildcard characters % and _, at least in the context of WHERE LIKE.

1 Like

Newer to SQLite here, but the \ escape character wasn’t working for me at least on the codecademy site version of sqlite.

Looked up a sqlite forum escape character and here’s what I learned and applied.
Using the ‘movies’ database from the Codecademy Queries lesson, I add two movies with the wildcard characters to the database

INSERT INTO movies
VALUES(999, ‘The 1%’, ‘drama’, 2023, 3.5);
INSERT INTO movies
VALUES(1000, ‘The_Code_Breaker’, ‘drama’, 2022, 5);

I check that both movies are successfully added to the database.

SELECT * FROM movies
WHERE id >500;

I define and use an escape character before a ‘%’ or a ‘_’.

WHERE name LIKE ‘%~%’ ESCAPE ‘~’;

After writing the WHERE statement, I add ESCAPE and single-quotes around a character we are not looking for. I place the new escape character ahead of the wildcard that I want to search for. I use a tilde here, but a question mark or other character works as well.

–This query will match any titles that end with ‘%’.

WHERE name LIKE ‘%~%’ ESCAPE ‘~’;

–This query will match any titles that have ‘_’.

WHERE name LIKE ‘%~_%’ ESCAPE’~';