split one value to multiple value

Question

I have a variable I want split it into two different column on the basis of ~ tild and after split I want Again split with , Comma Like Below

declare @Remarks varchar(100) = 'Product1~2,Product2~1'

I have split function After using Split function

select value from fn_split(@Remarks,',')

My result is

value
Product1~2
Product2~1

But I want result Like

value     Qty
Product1  2
Product2  1

You will need to split your split string i.e. split the full string on a comma and split the results by tilde. See... https://stackoverflow.com/questions/21768321/t-sql-split-string-based-on-delimiter — cymorg, Jul 11 '17 at 15:31

score 0 · Accepted Answer · answered Jul 11 '17 at 15:46

Disclaimer: You can use the fn_split function, I am just not using it because of my version of SQL.

I know of no way to split into separate columns or than manually, so you can use a couple substring functions to accomplish what you are trying to do.

@Remarks varchar(100) = 'Product1~2,Product2~1', @Delimiter VARCHAR(1) = ','
DECLARE @Products TABLE(Product VARCHAR(MAX))

;WITH Split_CTE (startPostion, endPosition)
    AS (
        SELECT CAST(0 AS INT) AS startPostion
        ,CHARINDEX(@Delimiter, @Remarks) AS endPosition
        UNION ALL
        SELECT  endPosition + 1 
        ,CHARINDEX(@Delimiter, @Remarks, endPosition + 1) 
        FROM        Split_CTE
        WHERE       endPosition > 0
    )
INSERT INTO @Products
SELECT  SUBSTRING(@Remarks,startPostion, COALESCE(NULLIF(endPosition,0),LEN(@Remarks) + 1) - startPostion) AS [Data]
FROM Split_CTE

SELECT SUBSTRING([Product], CHARINDEX('~', [Product]) + 1, LEN([Product])) AS Id
,SUBSTRING([Product], 0, CHARINDEX('~', [Product])) AS Product
FROM @Products

score 0 · Answer 2 · answered Jul 11 '17 at 16:36

There's also a way to do this using XML that you might find interesting:

DECLARE @Remarks varchar(100) = 'Product1~2,Product2~1'

-- set up some variables for customizing the delimiters and parsing into XML
DECLARE @xml as xml
    ,@str as varchar(100)
    ,@str2 as varchar(100)
    ,@delimiter as varchar(10)
    ,@delimiter2 as varchar(10)

-- initialize using the values you provided
SET @delimiter ='~'
SET @delimiter2 =','
SET @str = @Remarks

-- convert your string to XML
SET @str2 = ('<val>'+replace(@str,@delimiter ,'</val><qty>')+'</qty>')
SET @xml = cast(('<rec>'+replace(@str2,@delimiter2 ,'</qty></rec><rec><val>')+'</rec>') as xml)

-- SQL using XQuery
SELECT 
  ref.value('val[1]', 'varchar(10)') AS value,
  ref.value('qty[1]', 'varchar(10)') AS quantity
FROM @xml.nodes('/rec') 
xmlData( ref )

And the result:

value      quantity
---------- ----------
Product1   2
Product2   1

(2 row(s) affected)

split one value to multiple value

2 Answers2