I've done some internet trawling, and some reading of the Postgres regex docs, but I'm at the stage where I think asking a question here is the best thing to do.
I have the following Ruby regex:
Which I am trying to convert into a Postgres regex. So far I have come up with:
As I found this in one article online:
+is a metacharacter in PostgreSQL, whereas
\+is a plain “plus” character.
and this in another:
The backslash is used to escape characters in PostgreSQL strings. So a regular expression like
\wthat contains a backslash becomes
\\wwhen written as a literal string in a PostgreSQL statement. To match a single literal backslash, you’ll need the regex
The total query I'm running is this:
SELECT email FROM users WHERE email !~ '\\A[^@\\s]\+@([^@\\s]+\\.)\+[^@\\W]\+\\z'
And I'm expecting it to return zero results, bit it looks like it is selecting all email addresses.
I'm expecting no results because I've used the operator
!~ (I've also tried
!~*), which I think returns non-matches, and because I'm pretty sure that all the email addresses in my database do match the Ruby regex I pasted at the top of this question. (This in turn is because we use Devise in a Rails app to validate all user emails, with the default email validation settings)
So I've come to the conclusion that I've not converted my regex format/syntax correctly for Postgres, but also that I don't know what else I'm missing. Please can you help me understand whether this is possible, and if so, what else I need to change.
Once I have successfully translated that regex, I have a second Ruby regex which I also want to translate into Postgres, which is this one:
This is because I want to find all the user emails that don't have a valid email address according to the spec here: https://html.spec.whatwg.org/multipage/input.html#valid-e-mail-address
Thanks so much in advance 🙏