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
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)