CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlstored-procedures
GeoVIP
GeoVIP

Reputation: 1564

How to split two data into table

in stored procedure have query like this :

declare @col1_list varchar(max) , @col2_list varchar(max)
declare @tbl TABLE (col1 int , col2 int)

set @col1_list = '2|6|7|4|3|'
set @col2_list = '1|'

want split data into table , it should be like 

col1  col2
2      1
6      1
7      1
4      1
3      1

maybe col1 have one value in list and many values in col2 for example

 set @col1_list = '2|'
 set @col2_list = '1|2|3|4'

it should be like :

col1  col2
2      1
2      2
2      3
2      4

How to do it ?

Edit : *My version without function , using XML*

declare @col1_list varchar(max) , @col2_list varchar(max)
declare @tbl TABLE (col1 int , col2 int)

set @col1_list = '2|6|7|4|3|'
set @col2_list = '1|'

DECLARE @myXML1 AS XML = N'<H><r>' + REPLACE(@col1_list, '|', '</r><r>') + '</r></H>'
DECLARE @myXML2 AS XML = N'<H><r>' + REPLACE(@col2_list, '|', '</r><r>') + '</r></H>';

with mycte as (SELECT Vals1.id.value('.', 'NVARCHAR(50)') AS val1
FROM @myXML1.nodes('/H/r') AS Vals1(id)),
mycte1 as (SELECT Vals2.id.value('.', 'NVARCHAR(50)') AS val2
FROM @myXML2.nodes('/H/r') AS Vals2(id))

insert into @tbl (col1,col2)
select val1,val2
from mycte,mycte1
where val1<>'' and val2<>''

select * from @tbl

Upvotes: 0

Views: 64

Answers (1)

Hadi Sharifi
Hadi Sharifi

Reputation: 1527

Try this:

CREATE FUNCTION [dbo].[ufnSplit] (@string NVARCHAR(MAX))
RETURNS @parsedString TABLE (rowNo INT,val NVARCHAR(MAX))
AS 
BEGIN
   DECLARE @separator NCHAR(1)
   SET @separator='|'
   DECLARE @position int
   SET @position = 1
   DECLARE @i INT
   SET @string = @string + @separator
   SET @i=1
   WHILE charindex(@separator,@string,@position) <> 0
      BEGIN
         INSERT into @parsedString
         SELECT @i, substring(@string, @position, charindex(@separator,@string,@position) - @position)
         SET @position = charindex(@separator,@string,@position) + 1
         SET @i=@i+1
      END
     RETURN
END

For example:

SELECT ISNULL(t1.val, MAX(t2.val) OVER ()), ISNULL(t2.val, MAX(t2.val) OVER () )
FROM
      (SELECT * FROM  [dbo].[ufnSplit]('1|2|3')) t1
  FULL OUTER JOIN
      (SELECT * FROM  [dbo].[ufnSplit]('4')) t2
  ON t1.rowNo = t2.rowNo

Result:

col1    col2
----    -----
1       4
2       4
3       4

Upvotes: 1

Related Questions