0

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:

   hay@line.com
   u@y.com
   live.gmail.com

How do write this?

UpwardD
  • 739
  • 4
  • 12
  • 36
  • 1
    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

2 Answers2

3

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,'hay@line.com,u@y.com,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   hay@line.com
1   u@y.com
1   live.gmail.com

Or Simply

Select * from [dbo].[udf-Str-Parse]('hay@line.com,u@y.com,live.gmail.com',',')

Returns

RetSeq  RetVal
1       hay@line.com
2       u@y.com
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',' ')
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
1

Use Replace()

select replace(MyEmailField, '<CurrentDelimeter>', char(13)) as NewEmail
from MyTable
JohnHC
  • 10,935
  • 1
  • 24
  • 40