Line break or Carriage return in a Delimited Field in Sql

Question

I have an email column that stores a minimum of more than 10 emails in a row. Now, I want to write a query that puts each email on a separate line, e.g:

   [email protected]
   [email protected]
   live.gmail.com

How do write this?

This is a very bad design. You should fix that first by making the email a separate table and putting each email in an individual row. — HLGEM, Oct 07 '16 at 13:45
I agree with @HLGEM, bad database design. However, this may help you with your situation http://stackoverflow.com/questions/7595826/sql-server-table-result-to-array-in-sql-server-2005 — tale852150, Oct 07 '16 at 13:47

score 3 · Answer 1 · answered Oct 07 '16 at 13:49

If you mean rows of data... Any Parse/Split function will do if you don't have 2016. Otherwise the REPLACE() as JohnHC mentioned

Declare @YourTable table (ID int,Emails varchar(max))
Insert Into @YourTable values
(1,'[email protected],[email protected],live.gmail.com')

Select A.ID
      ,EMail=B.RetVal
 From  @YourTable A
 Cross Apply [dbo].[udf-Str-Parse](A.EMails,',') B

Returns

ID  EMail
1   [email protected]
1   [email protected]
1   live.gmail.com

Or Simply

Select * from [dbo].[udf-Str-Parse]('[email protected],[email protected],live.gmail.com',',')

Returns

RetSeq  RetVal
1       [email protected]
2       [email protected]
3       live.gmail.com

The Function if Needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

score 1 · Accepted Answer · answered Oct 07 '16 at 13:42

1

Use Replace()

select replace(MyEmailField, '<CurrentDelimeter>', char(13)) as NewEmail
from MyTable

answered Oct 07 '16 at 13:42

JohnHC

10,935
1
24
40

Line break or Carriage return in a Delimited Field in Sql

2 Answers2