Click here to Skip to main content
15,919,613 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Stdid	Firstname		Email
1	Vivek	         	vivek@abc.com
2	Pankaj	        	pankaj@gmail.com
3	Amit	         	amit@yahoo.com
4	Manish		        manish@hotmail.com
5	Abhishek		    abhishek@abcd.com

I need my result like 

Stdid	Firstname       part1       part2   part3
1	Vivek	          vivek         @abc     .com
2	Pankaj            pankaj       @gmail    .com
3	Amit	          amit         @yahoo    .com
4	Manish		      manish       @hotmail  .com
5	Abhishek          abhishek     @abcd     .com


The part1 can be off many charcters so it must analyse the words before @ and second part must be off @followed by domain name till before. alone and '.com' must always be recoganised in third part
Posted

Try:
SQL
SELECT  stdid, 
        Firstname, 
        SUBSTRING(Email, 1, CHARINDEX('@', Email,1) - 1) AS Part1,
        SUBSTRING(Email, CHARINDEX('@', Email,1), CHARINDEX('.', Email,1) - CHARINDEX('@', Email,1)) AS Part2,
        SUBSTRING(Email, CHARINDEX('.', Email,1), 9999) AS Part3
FROM MyTable
 
Share this answer
 
Hello ,
You may use CHARINDEX and SUBSTRING to get required op.

declare @name varchar(30)
set @name='abhishek@abcd.com'


declare @a varchar(20)
declare @b varchar(20)
declare @c varchar(20)

--get the first part
select @a=substring(@name,1,charindex('@',@name)-1)

--get the second part
select @b=substring(@name,charindex('@',@name),charindex('.',@name)-charindex('@',@name))
--get the third part

select @c=substring(@name,charindex('.',@name),len(@name)-charindex('.',@name)+1)

select @a,@b,@c
 
Share this answer
 
Try this:
SQL
SELECT  stdid,
        Firstname,
        LEFT(Email, CHARINDEX('@', Email,1) - 1) AS Part1,
        SUBSTRING(Email, CHARINDEX('@', Email), CHARINDEX('.', Email) - CHARINDEX('@', Email)) AS Part2,
        RIGHT(Email, CHARINDEX('.', REVERSE(Email))) AS Part3
FROM MyTable
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900