I need to locate all '@' in the login name field

We have a field called 'login_name', varchar 50 null.
I need to know which fields have any special characters but numbers are ok. - they should only have
their first initial and last name.
Some of them have the email address @/
but also some other special characters.

The data is supposed to be like this. For John Smith, = jsmith

But some staff may have jsmith@myworkcompany.org

or bad data. Bad data would be jsmith$, jsmith# etc.
but jsmith1 is good.

Provide some sample data and let us know what outcome you need?

1 Like

If you only want to allow letters and numbers, then:

WHERE login_name LIKE '%[^0-9A-Za-z]%' /*only values that DO have a NONalpha/NONnum char*/


CASE WHEN login_name LIKE '%[^0-9A-Za-z]%' THEN 'has special char' ELSE 'OK' END

Thank you, that is a beauty.

1 Like