How do you identify a set of 2 or 3 digits within text? postgresSQL

I would like to find instances where there are 2 or 3 digits in the "description".

The table is like

id | description  --- ------------  1 | This is 40cm long.   2 | The dog is black. 

I think that this should be a simple query and would appreciate any help.

I have tried

SELECT id, description FROM table WHERE description LIKE '%[0-9]%' or description LIKE '%[0-9][0-9]%' 

Which returns no result though I think it should return

id | description  --- ------------  1 | This is 40cm long.  

Thank you

Add Comment
2 Answer(s)

In Postgres, use regular expressions. This matches 1 or two digits (as in the code):

WHERE description ~ '[0-9]{1,2}'  

This matches 2 or 3 (as in the description):

WHERE description ~ '[0-9]{2,3}'  
Answered on August 30, 2020.
Add Comment

You are confusing LIKE with regular expressions. You can go with the regular expression route, or look at the SQL standard SIMILAR TO:

WHERE description SIMILAR TO '%[0-9]{2,3}%' 

Should get you descriptions with 2 or 3 digits

Source: https://www.postgresql.org/docs/current/functions-matching.html

Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.