can any one tell me how to split the given @parameter data into three column of table?

declare @parameter varchar (200)
set @parameter ='1_2_3|4_5_6'
;WITH CTE1 AS(
SELECT LEFT(items, CHARINDEX('_', items)-1) AS ss, SUBSTRING(items, CHARINDEX('_', items)+1 ,100) as col,SUBSTRING(items, CHARINDEX('_', items)+1 ,100) as col1
FROM dbo.characterSplit(@parameter,'|')
)

--INSERT INTO tblAdBlock(AdSpaceId ,Row ,[Column])
SELECT *
FROM CTE1 ;

ALTER FUNCTION [dbo].[characterSplit](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end

declare @parameter varchar (200)
set @parameter ='1_2_3|4_5_6'

SELECT ss=MAX(CASE c.itemnumber WHEN 1 THEN c.item END)
,col=MAX(CASE c.itemnumber WHEN 2 THEN c.item END)
,col1=MAX(CASE c.itemnumber WHEN 3 THEN c.item END)
FROM (SELECT @parameter) a(parameter)
CROSS APPLY dbo.DelimitedSplit8k(parameter, '|') b
CROSS APPLY dbo.DelimitedSplit8k(item, '_') c
GROUP BY b.ItemNumber

declare @parameter varchar (200)
set @parameter ='1_2_3|4_5'

SELECT ss=MAX(CASE c.itemnumber WHEN 1 THEN c.item END)
,col=MAX(CASE c.itemnumber WHEN 2 THEN c.item END)
,col1=MAX(CASE c.itemnumber WHEN 3 THEN c.item END)
FROM (SELECT @parameter) a(parameter)
CROSS APPLY dbo.DelimitedSplit8k(parameter, '|') b
CROSS APPLY dbo.DelimitedSplit8k(item, '_') c
GROUP BY b.ItemNumber

declare @parameter varchar (200)
set @parameter ='1_2_3|4_5'

SELECT ss=MAX(CASE c.itemnumber WHEN 1 THEN c.item END)
,col=MAX(CASE c.itemnumber WHEN 2 THEN c.item END)
,col1=MAX(CASE c.itemnumber WHEN 3 THEN ISNULL(c.item,0) END)
FROM (SELECT @parameter) a(parameter)
CROSS APPLY dbo.DelimitedSplit8k(parameter, '|') b
CROSS APPLY dbo.DelimitedSplit8k(item, '_') c
GROUP BY b.ItemNumber

sivajii (8/27/2012)

thanks
dwain.c

it was working fine
can u plz give one idea is there any chance to avoid null value instead of that replace 0 there in column in this proc itself

declare @parameter varchar (200)
set  @parameter ='1_2_3|4_5'

SELECT ss=MAX(CASE c.itemnumber WHEN 1 THEN c.item END)
    ,col=MAX(CASE c.itemnumber WHEN 2 THEN c.item END)
    ,col1=MAX(CASE c.itemnumber WHEN 3 THEN c.item END)
FROM (SELECT @parameter) a(parameter)
CROSS APPLY dbo.DelimitedSplit8k(parameter, '|') b
CROSS APPLY dbo.DelimitedSplit8k(item, '_') c
GROUP BY b.ItemNumber

i am getting output like this

ss	col	col1
1	2	3
4	5	NULL

and trying output like this

ss	col	col1
1	2	3
4	5	0

i tried like this

declare @parameter varchar (200)
set  @parameter ='1_2_3|4_5'

SELECT ss=MAX(CASE c.itemnumber WHEN 1 THEN c.item END)
    ,col=MAX(CASE c.itemnumber WHEN 2 THEN c.item END)
    ,col1=MAX(CASE c.itemnumber WHEN 3 THEN  ISNULL(c.item,0) END)
FROM (SELECT @parameter) a(parameter)
CROSS APPLY dbo.DelimitedSplit8k(parameter, '|') b
CROSS APPLY dbo.DelimitedSplit8k(item, '_') c
GROUP BY b.ItemNumber

declare @parameter varchar (200)
set @parameter ='1_2_3|4_5'

SELECT ss=isnull(MAX(CASE c.itemnumber WHEN 1 THEN c.item END), 0)
,col=isnull(MAX(CASE c.itemnumber WHEN 2 THEN c.item END), 0)
,col1=isnull(MAX(CASE c.itemnumber WHEN 3 THEN c.item END), 0)
FROM (SELECT @parameter) a(parameter)
CROSS APPLY dbo.DelimitedSplit8k(parameter, '|') b
CROSS APPLY dbo.DelimitedSplit8k(item, '_') c
GROUP BY b.ItemNumber