SQL – how to limit query with LIKE to select a certain number of digits after the %?

I’m trying to parse model names from a table using LIKE. Each model value looks like new_model_01215, new_model_01557 etc. Where new_model_01 part will be the same for all values. And there will always be only 3 numbers after the 01.

I’ve created the following statement and it works almost as expected. But the problem is that it returns values having more than 5 digits in a postfix.

What should I change in the query to return values with no more than 3 digits after the 01%?

SELECT model_name FROM models_table WHERE model_name LIKE 'new_model_01%'

Answer

You need to use a regular expression for that. In Postgres this can be done using ~ or similar to:

SELECT model_name 
FROM models_table 
WHERE model_name ~ 'new_model_01[0-9]{3}'

Advertisement

Answer