SQL trigger cutting email after @

Question

I have a trigger, that checks if the domain of the email, exists in another table (domain).

Table user:

+------------------+ +------------------+
| email            | | domain           |
+------------------+ +------------------+
| Joe@gmail.com    | | gmail.com        |
| Jack@live.com    | | live.com         |
| Berti@outlook.com| |------------------|
|------------------|

The trigger is:

IF EXISTS ( 
            SELECT 1 
            FROM [USER] AS U INNER JOIN inserted AS I 
            ON U.EMAIL = I.EMAIL
            WHERE REPLACE(RIGHT(U.EMAIL, CHARINDEX('@', U.EMAIL)-2),'_',' ') NOT IN (
                SELECT DOMAIN_NAME
                FROM ALLOWED_DOMAIN
            )
        )

The trigger doens't work correctly so i runned the following query:

 select REPLACE(RIGHT(U.EMAIL, CHARINDEX('@', U.EMAIL)-2),'_',' '), EMAIL
 from [USER] U

Which gives the following result:

| unknown        | email            |
+----------------|---------------   |
| gmail.com      | Joe@gmail.com    | 
| ive.com        | Jack@live.com    |
| ti@outlook.com | Berti@outlook.com|
+----------------|------------------|

For now it looks like the trigger applies the amount of characters from the 1st record to all records in the table... How can i fix the trigger so it works multiple row?

`RIGHT(U.EMAIL, len(u.email) - CHARINDEX('@', U.EMAIL))` will give the domain part — Serg, Jun 01 '17 at 10:21

score 2 · Answer 1 · answered Jun 01 '17 at 10:17

2

this should be the right statement

using reverse and right

declare @email as varchar(max) = 'abc@123456.com'

select REPLACE(Right(@email, CHARINDEX('@', reverse(@email))-1),'_',' ')

answered Jun 01 '17 at 10:17

RoMEoMusTDiE

4,739
1
17
26

score 2 · Accepted Answer · answered Jun 01 '17 at 10:18

Try this:

drop table if exists dbo.Email;


create table dbo.Email (
    email varchar(100)
);


insert into dbo.Email (email)
values ('Joe@gmail.com')
, ('Jack@live.com')
, ('Berti@outlook.com')

select
  RIGHT(U.EMAIL, len(u.email) - CHARINDEX('@', U.EMAIL))
from dbo.Email u

score 2 · Answer 3 · answered Jun 01 '17 at 10:30

The issue is that you are using CHARINDEX to find the distance from the beginning of the string and then using that to count from the end of the string in the RIGHT function.

You will need to subtract the number from the length of the string like this

select REPLACE(RIGHT(U.EMAIL,Len(U.Email) - CHARINDEX('@', U.EMAIL)),'_',' '), EMAIL 
From [User] U

Or just

select RIGHT(U.EMAIL,Len(U.Email) - CHARINDEX('@', U.EMAIL)), EMAIL
from [USER] U

score 1 · Answer 4 · answered Jun 01 '17 at 10:17

1

You can use SUBSTRING

DECLARE @Val NVARCHAR(100) = 'Berti@outlook.com'
SELECT SUBSTRING(@Val, CHARINDEX('@', @Val, 0) + 1, 1000) -- outlook.com

After @ character you can return all characters.

answered Jun 01 '17 at 10:17

neer

4,031
6
20
34

score 1 · Answer 5 · answered Jun 01 '17 at 10:36

1

declare @email varchar(50) = 'Berti@outlook.com'
select SUBSTRING(@email, CHARINDEX('@' ,@email) +1 , Len(@email) -  CHARINDEX(@email,'@'))

answered Jun 01 '17 at 10:36

Arsalan Qaiser

426
2
7
26

SQL trigger cutting email after @

5 Answers5