Can we apply the LIKE operator to values other than TEXT?

Question

Can we apply the LIKE operator to values other than TEXT?

Answer

Yes, you can apply the LIKE operator to numerical values as well.

Whenever you use LIKE however, you must always wrap the pattern within a pair of quotations, whether for matching a number or a string.

Example

/* 
This will select movies where the id number
starts with 2 and is followed by any two numbers.
*/
SELECT * 
FROM movies
WHERE id LIKE '2__';
25 Likes

entonces WHERE puntuación LIKE '2__ ';
seria parecido a hacer WHERE puntuación >=200 AND puntuación < 300;
???

7 Likes

Si, creo que tienes razon!

1 Like

How do we match values that have the _ (or %) literals in them?

4 Likes
11 Likes

Buenos Días @betajumper03322!
Sí, es correcto!
Gracías por tu pregunta muy interesante.

Can we apply the LIKE operator to textual values where we don’t have a limit to the Text length?

For eg, to get values in a column where the textual values should start from ‘Se’ and the length of the value can be infinite

5 Likes

Yes you would use the %

As an example, the below codes brings back all films in the movies database beginning with the letters star

SELECT *
FROM movies
WHERE name LIKE ‘star%’;

Hope this helps.

9 Likes

For this, why did you write it this way "2__" instead of writing it this way "2%"?

Thanks

3 Likes

I think it’s because

“2__” matches exactly two characters after 2,
“2%” matches unlimited characters after 2.

Therefore the meaning is slightly different. Hope it helps :slightly_smiling_face:

14 Likes

Thanks. That was helpful.

1 Like

So can we use LIKE operator with integers? I could not find the answer. Thanks in advance!

yes you can. You provide LIKE with a pattern which quoted and quotes are text - hence likely under the hood we can see that values converter to strings and it utilize similar process like in regex to find the pattern

1 Like

Thank you that was helpful :slight_smile:

Si, pero mira, si quieres usar este ejemplo es mas utilisado:

SELECT *
FROM movies
WHERE id LIKE ‘2_5’;

en este situacion es mas facile a usar LIKE no?

Why do we need to have the command/clause/operator capitalized although sql takes in normal/usual English writing style?
and do we have to follow the same format as given in the examples or can we write the command in different format like for SELECT *
FROM
WHY DONT WE just write Select * From movies where id like ‘2.5’?

I believe it is a convention that is meant to make code easier to read and understand. If you are looking at huge blocks of code it makes it much easier to see what’s going on if they aren’t all in a line in lower case letters. The caps make the commands stand out and the individual lines separate out commands and operators so it is easier to interpret later.

1 Like

Just a little follow up on % -

Can you use the % in-between characters? For example,

Last names: Johnson, Jackson, Jorgenson, Haeger

WHERE last_name LIKE ‘J___son’;

This would produce Johnson and Jackson. Could I instead use -

WHERE last_name LIKE ‘J%son’;

and produce Johnson, Jackson and Jorgenson?

Thank you!

1 Like

Cool, thank you Jephos

Great. let me give another example. Let’s say you have a table called “employees” with a column called “phone_number”. You can use the LIKE operator to find all phone numbers that start with a certain area code.

For example, if you want to find all employees with phone numbers that start with the area code “555”, you can use the following query:


SELECT * FROM employees WHERE phone_number LIKE ‘555%’

This query will return all employees whose phone numbers start with “555”. The “%” symbol is a wildcard character that matches any sequence of characters, so in this case it matches any phone number that starts with “555” and is followed by any other characters.