Extract name and prename from email address?

Question

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

Answer 1

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

Answer 2

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)