Solved

Find Semi-Colon and split into temp table

Posted on 2008-09-30
5
676 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:sqlman08
5 Comments
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
0
 
LVL 3

Expert Comment

by:DigitalSnorkle
Comment Utility
0
 
LVL 6

Accepted Solution

by:
Ashok Kumar earned 500 total points
Comment Utility
use can make use the below Function which will split based on a delimiter

create FUNCTION [dbo].[GetValuesFromList](
                    @List as Varchar(4000)
                    )

RETURNS @Table Table(Value Varchar(50))

AS    
Begin
     Declare @Sep          Varchar(1)
     Declare @Loc          Int
     Declare @Value     Varchar(100)
         
     
     If LTrim(RTrim(@List)) <> ''
     Begin
     
          Set @Sep = ';'  --- Mention the delimiter
          Set @List = @List + @Sep
         
          While PatIndex('%'+@Sep+'%',@List) <> 0
          Begin
               Set @Loc  =      PatIndex('%'+@Sep+'%',@List)
               Set @Value =     LTrim(Left(@List,@Loc-1))
               Set @List =      Stuff(@List,1,@Loc,'')

               
               
               Insert Into @Table(Value) Values (@Value)
     
          End --While
     End --If
     RETURN
End

go

declare @myArry varchar(1000)
select @myArry ='Oracle;Bill Gates;Windows;SQL 2008;ASP.NET'

select * from dbo.GetValuesFromList(@myArry)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
Usage:


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

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 34
Access recordset not updateable 8 38
Convert int to military time 8 20
MSSQL Frequency of Years From Days Field 2 11
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now