CODEWITHSUNDEEP
sqlsql-server
Stefan Steiger
Stefan Steiger

Reputation: 82166

Extract name and prename from email address?

Question: I have a table with the columns ID, Name, Prename, Mail

Mail contains the e-mail address of a person, for example [email protected]

Now I need to check whether Name='' or Prename='' and extract "John" from Mail and put it into Prename, and put "Doe" into column Name

Can i do that with SQL, if yes how ?

I use MS-SQL 2005

Upvotes: 1

Views: 3518

Answers (2)

p.campbell
p.campbell

Reputation: 100567

You can use an UPDATE statement as follows. 

--relies on one and only one dot in the email account!
UPDATE  Customer
SET  PreName = LEFT(Email, CHARINDEX('.',Email)-1) --FirstName
     ,Name = SUBSTRING(Email,CHARINDEX('.',Email)+1, CHARINDEX('@',Email)-CHARINDEX('.',Email)-1)

To test this solution, try with a single string as a test.

--test it out with this script!
DECLARE @Addr varchar(100)

SELECT @Addr = '[email protected]'

DECLARE @DotAt int, @At int

SELECT @DotAT = CHARINDEX('.',@Addr)
       ,@At = CHARINDEX('@',@Addr)

SELECT  LEFT(@Addr, @DotAt-1), SUBSTRING(@Addr,@DotAt+1,@At-@DotAt-1)

Upvotes: 2

Daniel Schaffer
Daniel Schaffer

Reputation: 57822

It is possible. You'll need to do some string manipulation. Check out the SUBSTRING, PATINDEX and CHARINDEX functions.

Upvotes: 0

Related Questions