-1

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%'
samba
  • 2,821
  • 6
  • 30
  • 85
  • Sounds like a regex match would help. Check out https://stackoverflow.com/questions/24368404/regular-expression-in-postgresql-like-clause. – Dave Costa Aug 30 '19 at 14:24

4 Answers4

3

In postgresql you can use the _ (underscore) character to match a single character, so three in a row would match your strings:

    SELECT model_name FROM models_table WHERE model_name LIKE 'new_model_01___'
daShier
  • 2,056
  • 2
  • 8
  • 14
  • The underscore will match not only digits but any char. – forpas Aug 30 '19 at 14:31
  • @forpas, you are correct that the wildcard matches any single character, but matching only digits was not the issue for the OP - only matching a specific number of digits, so I suggested the wildcard as being more efficient for the task than using regexp. – daShier Aug 30 '19 at 14:35
1

Here is one way to do it

SELECT model_name 
FROM models_table 
WHERE LEFT(model_name,LEN(model_name)-3)='new_model_01'
Radagast
  • 5,102
  • 3
  • 12
  • 27
0

The curly brackets represents length of the input. Can use regex_matches(..) to match a regex 

   SELECT model_name FROM 
    models_table WHERE 
   regexp_matches(model_name,  
   'new_model_01[0-9]{3}%') is not null
Himanshu
  • 3,830
  • 2
  • 10
  • 29
0

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}'