Display if column has more than 1 @ sign

Question

I have a table which holds a bunch of client email addresses in a narrative field which is used within an intapp process which picks up the email address and mails the client.

Unfortunately there is no validation in this field so users can enter anything they like which then causes the intapp process to fall over.

I am currently trying to identify the problems lines to perform a clean up but I have hit a problem. There are some lines in the narrative which have 2 email addresses in (Which again causes intapp to fall over)

I have written a script which will return any rows which have spaces in and also any lines which do not have an @ sign in them (On the logic that emails do not have spaces and must have @ signs).

I do not however know how to search and return any rows which have 2 @ signs in the narrative field to display if a user has entered 2 email address'. Is this possible?

Is this possible?

Answer 1

SELECT * FROM your_table WHERE email LIKE '%@%@%';

Answer 2

how about comparing the length of the original vs original stripping out any "@" signs..

where NOT len( yourField ) - len( REPLACE( yourField,'@','')) = 1

This would get those that do not have an "@" sign, but also those that have more than one