I'm always splitting concatenated strings into rows/whatnot so I knocked the following function up to give me the (n)th value in a delimited string. Bung it in a loop and you get your insert-all-rows done in a few lines of mini-code... may be useful for some of you. Could substitute charindex for patindex to keep this fully on-topic I suppose!

ufn_midinstance(@string, @delimiter, @instance, @length)

@string = string to parse

@delimiter = obvious...

@instance = zero-based value in the delimiter string to return

@length = number of chars to return for the value, or use 0 to get the whole value up to the next delimiter

eg: select ufn_midinstance('hello:world:what:a:boring:example', ':', 2,0)

returns: 'what'

eg: select ufn_midinstance('hello:world:what:a:boring:example', ':', 2,2)

returns: 'wh'

Then you can wrap 'er up in a loop to insert with something like:

declare @string varchar(100)

set @string = 'hello:world:what:a:boring:example'

declare @loop int

set @loop = 0

while @loop <= len(@string) - len(replace(@string,':',''))

begin

insert into SomeTable (SomeColumn)

select dbo.ufn_midinstance(@string, ':', @loop, 0)

set @loop = @loop+1

end

-------ufn_midinstance--------

create function ufn_midinstance(@string nvarchar(4000), @delimiter char, @instance int, @length int)

returns nvarchar(4000)

as

begin

declare @i int

set @i = 0

while @i < @instance

begin

set @string = substring(@string, charindex(@delimiter, @string,1)+1, len(@string))

set @i = @i+1

end

declare @string2 nvarchar(4000)

if charindex(@delimiter, @string, 1)>=1 set @string2 = substring(@string, 1, charindex(@delimiter, @string,1)-1)

else set @string2 = @string

set @string = substring(@string, 1, @length)

declare @return nvarchar(4000)

if @length = 0 set @return = @string2 else set @return = @string

if @return = '' set @return = null

return @return

end

----------------------