SQL to re-format email address

user_4408559 0
Viewed 74

I would like to put together SQL Select statement to reformat email addresses from the format smith_john@test.com to be formatted as john.smith@test.com. Obviously the number of characters in the first and last name are going to change, but the underscore character (_) will identify the addresses I am seeking to update.

What is the best way I can accomplish this? The email address field (EMAIL_ADD) currently contains the entire email address string.

sql-server-2014 sql
2 Answers

Building on @WamLeslie's answer

CROSS APPLY helps a lot here because you can use the same expression multiple times

SELECT
   SUBSTRING(
       email,
       v1.underscore + 1,
       v2.atsign - v1.underscore - 1
   ) --first name
   + '.'
   + LEFT(email, v1.underscore - 1)  -- last name
   + RIGHT(email, LEN(email) - v2.atsign + 1)  --domain
FROM table
CROSS APPLY (VALUES( NULLIF(CHARINDEX('_', email), 0) )) AS v1(underscore)
CROSS APPLY (VALUES( NULLIF(CHARINDEX('@', email, v1.underscore), 0) )) AS v2(atsign)
user_148689970

I've broken it out into its components so it's a bit easier to see.

declare @email varchar(100) = 'smith_john@test.com'

SELECT SUBSTRING(@email, CHARINDEX('_',@email )+1, CHARINDEX('@',@email )-CHARINDEX('_', @email)-1) --first name
       + '.'
       + LEFT(@email,CHARINDEX('_',@email )-1)  -- last name
       + RIGHT(@email, LEN(@email)- CHARINDEX('@', @email)+1)  --domain
user_108640930
Related