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

Question

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

Sounds like a regex match would help. Check out stackoverflow.com/questions/24368404/…. — Dave Costa
– Dave Costa, Commented Aug 30, 2019 at 14:24

daShier · Accepted Answer · 2019-08-30 14:27:55Z

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

answered Aug 30, 2019 at 14:27

daShier

2,1062 gold badges10 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

forpas Over a year ago

The underscore will match not only digits but any char.

daShier Over a year ago

@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.

Rajat · Accepted Answer · 2019-08-30 17:32:04Z

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'

edited Aug 30, 2019 at 17:32

answered Aug 30, 2019 at 15:29

Rajat

5,8733 gold badges14 silver badges30 bronze badges

Comments

user330315 · Accepted Answer · 2019-08-30 14:42:27Z

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

answered Aug 30, 2019 at 14:42

user330315

1 Comment

Himanshu Over a year ago

I just saw somewhere around that its better to avoid usage of similar to

Himanshu · Accepted Answer · 2019-08-30 15:25:09Z

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

edited Aug 30, 2019 at 15:25

answered Aug 30, 2019 at 14:26

Himanshu

3,9802 gold badges16 silver badges34 bronze badges

4 Comments

samba Over a year ago

what does {10} mean?

Himanshu Over a year ago

curly brackets indicates no of digits or length

user330315 Over a year ago

LIKE does not support regular expressions

Himanshu Over a year ago

umm.. never thought of like wouldnt support regular expressions as it supports wild card, patterns via %

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related