CODEWITHSUNDEEP
sql
Cyber--G33k
Cyber--G33k

Reputation: 19

Preventing duplicate '@' symbols in email address via SQL Query

Is there anyway (Query in SQL) that prevents multiple entry of '@' Symbol in Email Address OR Is there anyway (Query in SQL) that counts How many times '@' Symbol comes in an Email Address?

Upvotes: 0

Views: 226

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

In most databases you would use a check constraint:

create table . . . (
    . . .
    check (Email not like '%@%@%')

Or:

alter table . . .
    add constraint chk_NoDoubleAmpersands (Email not like '%@%@%'

If the database doesn't support check constraints, then you would use a trigger.

Upvotes: 1

Stephen Bodine
Stephen Bodine

Reputation: 519

This can easily converted to a stored procedure. [sql-2008]

This query will return the original text (email address) and the also include a column stating if the address passes the '@' test. This base code can also be modified to look if the substring after the '@' has at least one '.' IN THE ADDRESS STRING.

DECLARE @S VARCHAR(50) = '[email protected]'
DECLARE @testValue AS VARCHAR(50) = @S
DECLARE @PF AS BIT = 'FALSE'
WHILE PatIndex('%[^@]%', @testvalue) > 0
    SET @testValue = Stuff(@testValue, PatIndex('%[^@]%', @testValue), 1, '')
SET @PF = CASE WHEN LEN(@testValue) = 1 THEN 'TRUE' ELSE 'FALSE' END
IF @PF = 'TRUE' SET @PF = CASE WHEN CHARINDEX('.',@S,CHARINDEX('@',@S)) > 0 THEN 'TRUE' ELSE 'FALSE' END
SELECT @S, @PF AS is_valid

If more that one @ exist then the length of @testValue will be greater than 1. The @PF var first validates length = 1 else all other values fail. Next @PF if 'true' it checks if at least 1 '.' exist in the address after the '@' denoting a valid format. This by no means validates if the site and the tld (top level domain) exist.

Upvotes: 0

Related Questions