CODEWITHSUNDEEP
regexpostgresqlpattern-matching
Andrew Fox
Andrew Fox

Reputation: 133

Regex for last n characters of String in PostgreSQL query

Regex checks wouldn't be a strong point of mine. This is trivial but after playing around with it for 15 minutes already I think it would be quicker posting here. Ultimately I want to filter out any results of a table where a certain text column value ends with S(01 -99), i.e. the letter S followed by 2 digits. Consider the following test query

select x.* from (
select 
unnest(array['kjkjkj','jhjs01','kjkj11','kjhkjh','uusus','iiosis99']::text[]) 
as tests ) x
where RIGHT(x.tests,3) !~ 'S[0-9]{1,2}$'

This returns everything in the unnested array, whereas I'm hoping to return everything excluding the second and last values. Any pointers in the right direction would be much appreciated. I'm using PostgreSQL v11.9

Upvotes: 2

Views: 1495

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627580

You may actually use SIMILAR TO here since your pattern is not that complex:

SELECT * FROM table
   WHERE column_name NOT SIMILAR TO '%S[0-9]{2}'

SIMILAR TO patterns require a full string match, so here, % matches any text from the start of the string, then S matches S and [0-9]{2} matches two digits that must be at the end of the string.

If you were to use a regex, you could use

WHERE column_name !~ 'S[0-9]{2}$'

Or, 'S[0-9]{1,2}$' if there can be one or two digits. Since the regex search in PostgreSQL does not require a full string match, it just matches S, two (or one or two with {1,2}) digits at the end of string ($).

Upvotes: 3

Related Questions