Parsing assistance

[Joseph]

Hello! I'm looking for the most efficient manner to parse an email address. I'd like to take an email address and create three strings:

1. the string before the @ sign.
2. the string after @ and before . (period)
3. the string after the . (period)

Thanks in advance for your assistance,
Ben

[ScottPletcher]

SELECT email,
    LEFT(email, at_sign_loc - 1) AS string_before_at,
    SUBSTRING(email, at_sign_loc + 1, last_dot_loc - at_sign_loc - 1) AS string_after_at_before_dot,
    SUBSTRING(email, last_dot_loc + 1, LEN(email)) AS string_after_dot
FROM ( VALUES('abc@123.com'),('defgh@abc.def.ghk.com') ) AS test_data(email)
CROSS APPLY (
    SELECT CHARINDEX('@', email) AS at_sign_loc,
        LEN(email) - CHARINDEX('.', REVERSE(email)) + 1 AS last_dot_loc
) AS calcs1

[Joseph]

This beginner really appreciates your assistance!! Thank you!!

[harishgg1]

hi

i know the solution has given by Scott Pletcher

i have a different approach ( might not be the best )
if it helps great

drop create data .....

/***************************************************************************/


-- drop all temp tables 

DECLARE @SQL NVARCHAR(max) = (SELECT 'DROP TABLE ' + Stuff( ( SELECT ',' + NAME 
                                     FROM 
                                     tempdb.sys.tables FOR 
                                       xml path( 
          '') ), 1, 1, '')); 

--PRINT @SQL; 
EXEC Sp_executesql 
  @SQL; 


/***************************************************************************/
 
 -- create tables 
 -- INT VARCHAR(100)  DATE DATETIME DECIMAL(10,2) 

 create table #tablename
 (
 email_id VARCHAR(100)
 )
 go 

 /***************************************************************************/

 -- insert data into tables

 insert into #tablename select 'nauok4@gmail.com'
 
 insert into #tablename select 'surefine@yahoooooooooo.com'

/****************************************************************************/

-- select from all temp tables 

DECLARE @SQL1 NVARCHAR(max) = (SELECT Stuff((SELECT '; select * from ' + NAME + 
                                                    ' go ' 
                 FROM   tempdb.sys.tables 
                 FOR xml path('')), 1, 1, '')); 

--PRINT @SQL1; 
EXEC Sp_executesql 
  @SQL1; 

/***************************************************************************/

SQL .. a different way ...

/***************************************************************************/

-- SELECT Query SOLUTION 

select email_id,
       substring(email_id,1,PATINDEX('%@%', email_id)-1), 
	   substring(email_id,patindex('%@%',email_id)+1, patindex('%.%',email_id) -patindex('%@%',email_id)-1),
	   substring(email_id,patindex('%.%',email_id)+1,len(email_id)- patindex('%.%',email_id)+1)
from #tablename
/***************************************************************************/

[ScottPletcher]

Doesn't work. Try an email with a dot before the @ and you'll see what I mean:

insert into #tablename 'firstname.lastname@somecompany.com'

[harishgg1]

thanks Scott ,

I know my solution will not work in other types of data

i looked at what Joseph said in the begining
he did not mention anything like firstname.lastname@gmail.com ( i mean two dots )

i just "assumed" what he meant was all data would be like abc@gmail.com
based on that i gave my solution

[ScottPletcher]

But it's extremely common at large companies to use name.name@ for individual emails. There's no rule against using dots in either part of the name, so you really can't just assume they won't be used. That's not a valid assumption at all.

[harishgg1]

Hi Scott

I agree with you

Data comes in all shapes and forms

Thinking of all common scenarios
While creating data is also ok

I just did not think of common scenarios
No damage done ..