CODEWITHSUNDEEP
sqlsql-server
Synergy
Synergy

Reputation: 31

Splitting an email column into three columns in Sql Server

I've an email column values in employee table:

Email
[email protected]
[email protected]
[email protected]

I want to split the email into three columns like: 

FirstName        LastName              DomainName
--------------------------------------------------
regan            manning               @cresa.com
miang            luso                  @praxis.com
selin            robert                @cummins.com

Upvotes: 2

Views: 718

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use Substring & Charindex Function to check the ., @ & retrieve the data :

SELECT @email [Email],
       SUBSTRING(@email, 1, CHARINDEX('.', @email)-1) [FirstName],
       SUBSTRING(@email, CHARINDEX('.', @email)+1, CHARINDEX('@', @email)-CHARINDEX('.', @email)-1) [LastName],
       SUBSTRING(@email, CHARINDEX('@', @email), LEN(@email)) [DomainName] from <table_name>;

Result :

Email                   FirstName    LastName   DomainName
[email protected]   miang        luso       @praxis.com

Upvotes: 0

ashish2199
ashish2199

Reputation: 393

SELECT 

substring( @email , 0 , CHARINDEX('.' , @email) ) ,

substring( @email , CHARINDEX('.',@email)+1 , CHARINDEX('@' , @email) - CHARINDEX('.' , @email)-1 ),

substring( @email , CHARINDEX('@' , @email) , LEN(@email) - CHARINDEX('@' , @email)+1 )

from employee;

--replace @email with name of your email column

Easier version to understand:

DECLARE @email NVARCHAR(30) = '[email protected]';
DECLARE @dot INT =CHARINDEX( '.' , @email );
DECLARE @atrate INT =CHARINDEX( '@' , @email );
DECLARE @len INT =LEN( @email );

SELECT 
substring( @email , 0 , @dot ),
substring( @email , @dot+1 , @atrate - @dot-1 ),
substring( @email, @atrate , @len - @atrate+1 );

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

One method uses apply:

select t.*, v.domain, v2.firstname, v2.lastname
from t cross apply
     (values(stuff(email, 1, charindex('@', email), '') as domain,
             left(email, charindex('@', email)
            )
     ) v(domain, name) cross apply
     (values (left(name, charindex('.')),
              stuff(name, 1, charindex('.', name), '')
             )
     ) v2(lastname, firstname;

Upvotes: 2

Related Questions