Postgres pattern matching middle letters of a string

Question

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.

Do you mean SIMILAR TO '%[qwerty]%'? Could you share examples? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented May 15, 2020 at 8:48
middle letters? Does that mean all letters except the first and the last? Even for en.wikipedia.org/wiki/…? — jarlh
– jarlh, Commented May 15, 2020 at 8:51
LIKE doesn't support regular expressions. You need similar to or ~ if you want them. — user330315
– user330315, Commented May 15, 2020 at 8:53
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. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented May 15, 2020 at 8:59
I added another solution that may be close to what you want, but still does not fully adhere to the formula above — Wiktor Stribiżew
– Wiktor Stribiżew, Commented May 15, 2020 at 9:09

Laurenz Albe · Accepted Answer · 2020-05-15 10:12:15Z

3

A different solution that is maybe simpler to write is to use PostgreSQL's regular expression matching operator ~:

WHERE name ~ '.[qwerty].'

The pattern means: an arbitrary character, followed by any of the characters qwerty, followed by another arbitrary character.

Note that PostgreSQL translates SIMILAR TO expressions to regular expressions internally.

answered May 15, 2020 at 10:12

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wiktor Stribiżew · Accepted Answer · 2020-05-15 09:07:10Z

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.

Collectives™ on Stack Overflow

Postgres pattern matching middle letters of a string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related