Skip to content
Advertisement

Postgres pattern matching middle letters of a string

How would I match a name, such that one of the middle letters of the name must come from the word ‘qwerty'(thus containing either of the letters ‘q’, ‘w’ , ‘e’, ‘r’, ‘t’,’y’)?

I am curious how you can determine what the middle letters are, this would make use of some string count I assume.

What I have tried so far:

WHERE name LIKE '%(q|w|e|r|t|y)%'

Examples which would match:

Martens
Van Rossem
Frimout
Gates

Note: Middle letters in my exercise context means anything inbetween the first and last letter. I am still curious how one would match the ‘median’ letter of a string.

Advertisement

Answer

You seem to want to match the same amount of chars on the left and right and then some chars in the middle, but this is not possible with PostgreSQL regex.

To find records that contain any of the characters in a pre-defined set, you may use SIMILAR TO with a %[qwerty]% pattern:

SIMILAR TO '%[qwerty]%'

The % is a regular wildcard matching any text, and [qwery] is a bracket expression that matches a single character, either q, w, e, r, t or y.

If you just want to make sure these characters are not at the start and end, you may try an expression like

SIMILAR TO '[^qwerty]+[qwerty]+[^qwerty]+'

Keeping in mind that PostgreSQL SIMILAR TO patterns require the full string match, you will only get the records that start with 1+ chars other than q, w, e, r, t, y ([^...] is a negated bracket expression), then 1 or more required chars and then 1+ chars other than those.

4 People found this is helpful
Advertisement