regexpostgresql

Need a regex_match checks if word doesn't starts with letters R or W and contains 4 letters and 3 numbers


I need a match if in my text column I can find a word that contains 4 caracters followed with 3 digits, and the word did not started with the letter R or W (if possible on Postgresql)

I have made already a regexp_match, but it doesn't figured out what i need.

Like :

column should be returned
Thomas Hawk AQWS456 OK
Cecile Star RQWS456 KO
Mickey Mouse WQWS456 KO
Donald Duck SQWS456 OK

Here my RegEx Code : (?:[A-Za-z]{3,4}\d{3})

https://regex101.com/r/0DYIDs/2

THX in advance

A


Solution

  • PostgreSQL has the ~ operator which precisely does POSIX regexp matching.
    To make sure your code is an isolated word, make it preceded by a \s (whitespace) and followed by a (?:\s|$) (non capturing parentheses, for either whitespace or the end of the text):

    select *, case when label ~ '\s[A-QS-VX-Za-qs-vx-z][A-Za-z]{3}\d{3}(?:\s|$)' then 'OK' else 'KO' end result from t;
    
    label should result
    Thomas Hawk AQWS456 OK OK
    Cecile Star RQWS456 KO KO
    Mickey Mouse WQWS456 KO KO
    Donald Duck SQWS456 OK OK

    (play with it in this fiddle)