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.