asked on

Find Semi-Colon and split into temp table

Hi, I have a table with two columns, ID, Recipients.

ID      Recipients
1      badboy@microsoft.com
2      rudeboy@microsoft.com;bill.gates@microsoft.co.uk
3      nutter@oracle.com;paul.jones@oracle.co.uk;diamondgeezer@mobiles.com
4      itsupport@spectrum.com

What I want to do is create a another temporary table that will have the same columns but I
want the output to appear like below

ID      Recipients
1      badboy@microsoft.com
2      rudeboy@microsoft.com
2      bill.gates@microsoft.co.uk
3      nutter@oracle.com
3      paul.jones@oracle.co.uk
3      diamondgeezer@mobiles.com
4      itsupport@spectrum.com

So what I want it to do is to go through the recipients column and find if there is
a semi-colon ';' if there is that will mean there is more that one email address so will need
to appear as one row in the temp table.

Thanks

chapmandew

http://blogs.techrepublic.com.com/datacenter/?p=375

DigitalSnorkle

See here:
https://www.experts-exchange.com/questions/21987575/Split-string-into-array-of-strings-using-SQL.html?qid=21987575

ASKER CERTIFIED SOLUTION

Ashok Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrandonGalderisi

This will do it very fast without any looping, but requires a table of sequential numbers.

A table of sequential numbers is VERY useful in SQL Server not use for this. If you have a table like that already, replace the table name below in place of vw_nums and the column name containing the number in place of n.

if object_id('[dbo].[fn_DelimitedToTable]') is not null
     drop function [dbo].[fn_DelimitedToTable]
go
create function [dbo].[fn_DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
     )
as
begin
 
insert into @Values (thePosition,theValue)
		select n, substring(@delimiter + @DelimitedString + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @DelimitedString + @delimiter, n + len(@delimiter)) - n - len(@delimiter)) as string_value
		from	dbo.vw_Nums
		where
			n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
			and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter
 
 
 
return
end

Open in new window

BrandonGalderisi

Usage:

select theValue from [dbo].[fn_DelimitedToTable]('a;b;c;d;e;f;g',';')

Open in new window