Split Email with semicolon delimiter in SQL Server

Question

I have column which stores multiple Email with semicolon separated as below:

|             Email            |
| -----------------------------|
| abc@y.aa; abc@z.bb           |
| pqr@y.bb; pqr@x.cc; pqr@z.dd |
| xyx@y.dd; xyx@z.pp           |

I am looking to split email using delimiter semicolon and store them in different column.

I have tried a few string commands but it doesn't provide me an appropriate result

REVERSE(PARSENAME(REPLACE(REVERSE(Email), ';', '.'), 1)) AS [Email1],
REVERSE(PARSENAME(REPLACE(REVERSE(Email), ';', '.'), 2)) AS [Email2],
REVERSE(PARSENAME(REPLACE(REVERSE(Email), ';', '.'), 3)) AS [Email3]

Any leads on this will be appreciated.

If there is any better solution, please feel free to share it here.

Thanks in advance!

Please provide sample data and desired results. Also, is there a maixmum number of emails? — Gordon Linoff, May 13 '21 at 14:49
Depending on what version of SQL there is a string parse function that parses the data into a table, and you can select it from the table results but as Linoff mentioned, please provide data and results — Brad, May 13 '21 at 14:50
How many email addresses can there be? Seems like you should be normalising your data, nor replacing one denormalised method with another. — Thom A, May 13 '21 at 14:57
This is the case for few entries in DB where they entered multiple Emails. Yes Normalization is the option but that will require major changes in functionality. There are no more then 3 email entries in the column. Thanks! — cool_taps, May 13 '21 at 15:04
https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15 — gvee, May 13 '21 at 15:19
@cool_taps then change the broken functionality. It was broken from the start, so it has to be fixed sooner or later. What you have now simply can't be queried or indexed. If you don't intend to query the Email values though, you can split on the client side, and treat the field as a black box. — Panagiotis Kanavos, May 13 '21 at 15:34
While asking a question, you need to provide a minimal reproducible example. Please refer to the following link: https://stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky, May 13 '21 at 15:39
"This is the case for few entries in DB where they entered multiple Emails". I suppose because it was fewer it was less work for you to work around? No? Then normalization isn't about how many records are going to violate something and give you more headaches. It's that even one record can violate something and give you the same size headache. — pwilcox, May 13 '21 at 15:39
Does this answer your question? [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) — Charlieface, May 13 '21 at 16:16

score 2 · Answer 1 · edited May 13 '21 at 23:19

Here's two approaches:

    DECLARE @email TABLE (Email VARCHAR(1000));
    INSERT @email VALUES('abc@y.aa; abc@z.bb'),('pqr@y.bb; pqr@x.cc'),('xyx@y.dd; xyx@z.pp');
    
    --==== 1. Using APPLY
    SELECT 
      EmailGroup  = e.Email,
      Email       = email1.E1
    FROM   @email AS e
    CROSS APPLY (VALUES
        ( SUBSTRING(e.Email, 0, CHARINDEX(';', e.email)      ) ),
        ( SUBSTRING(e.Email, CHARINDEX(';', e.email) + 1, 8000) )
    ) AS email1(E1);
    
    --==== 2. Using STRING_SPLIT
    SELECT e.Email, split.[value]
    FROM   @email AS e
    CROSS APPLY STRING_SPLIT(e.Email,';') AS split;

Both Return:

EmailGroup              Email
----------------------- -------------
abc@y.aa; abc@z.bb      abc@y.aa
abc@y.aa; abc@z.bb      abc@z.bb
pqr@y.bb; pqr@x.cc      pqr@x.cc
pqr@y.bb; pqr@x.cc      pqr@y.bb
xyx@y.dd; xyx@z.pp      xyx@y.dd
xyx@y.dd; xyx@z.pp      xyx@z.pp

For both emails on the same row:

SELECT 
  EmailGroup = e.Email,
  Email1     = SUBSTRING(e.Email,0,CHARINDEX(';',e.email)),
  Email2     = SUBSTRING(e.Email,CHARINDEX(';',e.email)+1,8000)
FROM   @email AS e;

EmailGroup             Email1     Email2
---------------------- ---------- ---------------
abc@y.aa; abc@z.bb     abc@y.aa    abc@z.bb
pqr@y.bb; pqr@x.cc     pqr@y.bb    pqr@x.cc
xyx@y.dd; xyx@z.pp     xyx@y.dd    xyx@z.pp

@Alan Burstein - Thanks! second version is working. I am getting 2 emails correctly. In some cases, it has 3 email so what changes should I make to get Email3? — cool_taps, May 13 '21 at 17:15

John Cappelletti · Answer 2 · 2021-05-13T15:26:13.883

1

If you have a known, or maximum number of email addresses, you can use a bit of JSON

Example

Select A.[email]
      ,Email1 = JSON_VALUE(S,'$[0]')
      ,Email2 = JSON_VALUE(S,'$[1]')
      ,Email3 = JSON_VALUE(S,'$[2]')
 From YourTable  A
 Cross Apply ( values ( '["'+replace(replace(email,' ',''),';','","')+'"]' ) ) B(S)

Results

email               Email1      Email2      Email3
abc@y.aa; abc@z.bb  abc@y.aa    abc@z.bb    NULL
pqr@y.bb; pqr@x.cc  pqr@y.bb    pqr@x.cc    NULL
xyx@y.dd; xyx@z.pp  xyx@y.dd    xyx@z.pp    NULL

edited May 13 '21 at 15:26

answered May 13 '21 at 15:09

John Cappelletti

79,615
7
44
66

Thanks but giving an error - JSON text is not properly formatted. Unexpected character '"' is found at position 1. – cool_taps May 13 '21 at 15:38
@cool_taps Not clear on the error you're getting. Take a peek at https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=43164557a3c2c454bc357410ab1841ae – John Cappelletti May 13 '21 at 15:39
@cool_taps, please don't miss my comments talking about **minimal reproducible example**. – Yitzhak Khabinsky May 13 '21 at 15:43

score 0 · Answer 3 · answered May 13 '21 at 22:23

Try this:

Declare @testTable Table (Email varchar(100));
 Insert Into @testTable (Email)
 Values ('abc@a.aa')
      , ('abc@y.aa; abc@z.bb')
      , ('pqr@y.bb; pqr@x.cc; pqr@z.dd')
      , ('xyx@y.dd; xyx@z.pp');

 Select tt.Email
      , email1 = ltrim(substring(v.email, 1, p01.pos - 2))
      , email2 = ltrim(substring(v.email, p01.pos, p02.pos - p01.pos - 1))
      , email3 = ltrim(substring(v.email, p02.pos, p03.pos - p02.pos - 1))
   From @testTable                                              As tt
  Cross Apply (Values (concat(tt.Email, replicate(';', 3))))    As v(email)
  Cross Apply (Values (charindex(';', v.email, 1) + 1))         As p01(pos)
  Cross Apply (Values (charindex(';', v.email, p01.pos) + 1))   As p02(pos)
  Cross Apply (Values (charindex(';', v.email, p02.pos) + 1))   As p03(pos)

score 0 · Answer 4 · answered Jun 16 '22 at 13:53

You're close with the PARSENAME, however that function parses on periods and emails have periods in them which throws it off.

You can just replace those with another character and then flip them back to periods at the end.

For instance

 SELECT 
REPLACE(REVERSE(PARSENAME(REVERSE(REPLACE(REPLACE(Email, '.', '`'), ';', '.')),1)), '`','.') AS Email1,
REPLACE(REVERSE(PARSENAME(REVERSE(REPLACE(REPLACE(Email, '.', '`'), ';', '.')),2)), '`','.') AS Email2,
REPLACE(REVERSE(PARSENAME(REVERSE(REPLACE(REPLACE(Email, '.', '`'), ';', '.')),3)), '`','.') AS Email3,
REPLACE(REVERSE(PARSENAME(REVERSE(REPLACE(REPLACE(Email, '.', '`'), ';', '.')),4)), '`','.') AS Email4

Split Email with semicolon delimiter in SQL Server

4 Answers4