July 18, 2011 at 1:32 am
Hello Guys,
CREATE FUNCTION [dbo].[Splitchar](@String varchar(8000), @Commaseperator char(1))
returns @sampletable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @newstring varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@commaseperator,@String)
if @idx!=0
set @newstring = left(@String,@idx - 1)
else
set @newstring = @String
if(len(@newstring)>0)
insert into @sampletable(Items) values(@newstring)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
GO
Its works as follows:
select * from [Splitchar]('z,5',',')
result :
items
z
5
Thing is that my table Graduate has 3 fields as
GID GName AdditionalGID
69 Software 45,56
72 Software 75,65
I would like to split AdditionalGID and insert into the same graduate table as new rows with same data as :
GID GName AdditionalGID
45 Software NULL
56 Software NULL
can anyone helpme regarding this.
July 18, 2011 at 2:57 am
Step 1: search this site for DelimitedSplit8K. You'll find a much better performing function...
Step 2: Use that function together with CROSS APPLY
Something like the following code snippet (untested):
SELECT Graduate.GID, Graduate.GName, x.Item
FROM Graduate
CROSS APPLY
(SELECT Item from dbo.DelimitedSplit8K( AdditionalGID)) x
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply