SIMILAR TO

[Paulo Geloramo]

Simple regular expression to check an email list:
([a-z0-9\_\-\.]+@[a-z0-9\_\-\.]+[,|;]?)*

a...@server.com;he...@server.com;dmi...@server.com

I tested on the site https://regexr.com and passed.

Firebird 2.5.9 and 3.0.7
In Firebird I'm doing the command below this is working for most.

Failed when email has a minus sign -
Any suggestion?

SELECT *
FROM CLIENT
WHERE EMAIL NOT SIMILAR TO '([a-z0-9\_\-\.]+@[a-z0-9\_\-\.]+[,|;]?)*'
--
Paulo Esteves

[Dimitry Sibiryakov]

07.04.2021 16:50, Paulo Geloramo wrote:
> Failed when email has a minus sign -
> Any suggestion?
>
> SELECT *
> FROM CLIENT
> WHERE EMAIL NOT SIMILAR TO '([a-z0-9\_\-\.]+@[a-z0-9\_\-\.]+[,|;]?)*'

You forgot ESCAPE clause. It has no default.

--
WBR, SD.

[Mark Rotteveel]

On 07-04-2021 16:50, Paulo Geloramo wrote:
> Simple regular expression to check an email list:
> ([a-z0-9\_\-\.]+@[a-z0-9\_\-\.]+[,|;]?)*

A word of warning: the actual syntax of valid email addresses is a lot
more complex and - IIRC - is not a regular language so cannot be
captured using a regular expression.

Using a regular expression to check email addresses can exclude
addresses that are actually valid.

> a...@server.com;he...@server.com;dmi...@server.com
>
> I tested on the site https://regexr.com and passed.
>
> Firebird 2.5.9 and 3.0.7
> In Firebird I'm doing the command below this is working for most.
>
> Failed when email has a minus sign -
> Any suggestion?
>
> SELECT *
> FROM CLIENT
> WHERE EMAIL NOT SIMILAR TO '([a-z0-9\_\-\.]+@[a-z0-9\_\-\.]+[,|;]?)*'

The problem is that you seem to want to use `\` as the escape character,
but you haven't actually specified an escape character, as a result, the
`\` in your character class is just that, a `\`, and not an escape. In
addition, in the SQL regular expression language, the `.` is not a
special character, so it doesn't need to be escaped (and attempting to
do so will result in an error).

You need to add an escape clause (escape '\') and remove the `\` before
the `.`:

SIMILAR TO '([a-z0-9\_\-.]+@[a-z0-9\_\-.]+[,|;]?)*' escape '\'

Also, given the underscore has no special meaning in a character class,
it doesn't need to be escaped; this means you can use:

SIMILAR TO '([a-z0-9_\-.]+@[a-z0-9_\-.]+[,|;]?)*' escape '\'

To avoid case-sensitivity issues (i.e. you specify a-z, but not A-Z),
I'd recommend using the predefined class [:ALNUM:]:

SIMILAR TO '([[:ALNUM:]_\-.]+@[[:ALNUM:]_\-.]+[,|;]?)*' escape '\'

Mark
--
Mark Rotteveel

[Paulo Geloramo]

Mark Rotteveel,

Perfect, thanks for the feedback.

I would like to add the double point check:

Example:
mark..rotteveel@server.com

Any suggestion?
--
Paulo Esteves
Brazil

Em qua., 7 de abr. de 2021 às 12:42, Mark Rotteveel
<ma...@lawinegevaar.nl> escreveu:

> --
> You received this message because you are subscribed to the Google Groups "firebird-support" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
> To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/ba9e05ed-10de-c489-4e4d-3b7a4e69b292%40lawinegevaar.nl.

[Mark Rotteveel]

On 2021-04-07 19:48, Paulo Geloramo wrote:
> Mark Rotteveel,
>
> Perfect, thanks for the feedback.
>
> I would like to add the double point check:
>
> Example:
> mark..rotteveel@server.com
>
> Any suggestion?

The full syntax of email addresses are hard (if not impossible) to fully
check with regular expressions, more so with the SQL regular expressions
supported by Firebird, as it is missing some of the more advanced
features like (negative or positive) lookbehind and lookahead. To be
honest, I don't think you can do this. In Firebird 3 you could write a
UDR function that uses a more advanced regular expression engine (or
maybe a parser) to verify email addresses.

Mark