PostgreSQL regular expression with LIKE or similarto-CodePudding

I am trying to figure out the pattern match with postgresql LIKE operator to perform a search for particular pattern.

For example,

select usename  
FROM tablename 
where CAST(usename AS VARCHAR) LIKE  'abc__________'

What my table contains:

abc_akdufhdhlu
abc_edf_kliokkklos
edf_poiuyterdh
abc_lkjhgfgaha
abc_hhh_kaolloiaeh
abc_oasdhehwjwo

What I am getting

abc_akdufhdhlu
abc_lkjhgfgaha
abc_oasdhehwjwo

I want to achieve same thing without using abc__________.

Is there better way writing pattern matching in Postgres that match pattern: fixedString(abc_) followed by 10 random characters [a-zA-Z]. ex: abc_[a-zA-z]

CodePudding user response：

You can use this query if you want to get abc with any suffix

select usename  FROM tablename where CAST(usename AS VARCHAR) LIKE  'abc_' || '%'

If you want to do that two way(prefix and suffix)

select usename  FROM tablename where CAST(usename AS VARCHAR) LIKE  '%'||'abc_' || '%'

CodePudding user response：

You can either use the regex operator ~

select usename
from tablename
where usename ~ '^abc_[a-zA-Z]{10}$`

The ^ and $ are there to ensure the whole value is matched.

Alternatively you can use similar to

select usename
from tablename
where usename similar to 'abc_[a-zA-Z]{10}'

similar to always matches the whole string, so the ^ and $ are not necessary.