Split unlimited length SQL String into two columns-CodePudding

I have seen multiple answers, but none that worked for me.

I send in a string like this desc1$100$desc2$200 to a stored procedure.

Then I want to to insert it into a temp table like so:

|descr|meter|
|desc1|100  |
|desc2|200  |

Wanted output ^

declare @string varchar(max) 
set @string = 'desc1$100$desc2$200'


declare @table table
        (descr varchar(max),
            meter int
        )

    -- Insert statement
-- INSERT NEEDED HERE


-- Test Select
SELECT * FROM  @table

How should I split it?

CodePudding user response：

Here's an example using JSON.

Declare @S varchar(max) = 'desc1$100$desc2$200'

Select Descr = max(case when ColNr=0 then Value end )
      ,Meter = max(case when ColNr=1 then Value end )
 From (
        Select RowNr  = [Key] / 2
              ,ColNr  = [Key] % 2
              ,Value
         From  OpenJSON( '["' replace(string_escape(@S,'json'),'$','","') '"]' )
      ) A
 Group By RowNr

Results

Descr   Meter
desc1   100
desc2   200

If it helps with the visualization, the subquery generates the following:

RowNr   ColNr   Value
0       0       desc1
0       1       100
1       0       desc2
1       1       200

CodePudding user response：

Please try the following solution based on XML and XQuery.

It allows to get odd vs. even tokens in the input string.

SQL

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, descr varchar(max), meter int);

DECLARE @string varchar(max) = 'desc1$100$desc2$200';
DECLARE @separator CHAR(1) = '$';

DECLARE @xmldata XML = TRY_CAST('<root><r><![CDATA['   
      REPLACE(@string, @separator, ']]></r><r><![CDATA[')   
      ']]></r></root>' AS XML)

INSERT INTO  @tbl (descr, meter)
SELECT c.value('(./text())[1]', 'VARCHAR(30)') AS descr
    , c.value('(/root/*[sql:column("seq.pos")]/text())[1]', 'INT') AS meter
FROM @xmldata.nodes('/root/*[position() mod 2 = 1]') AS t(c)
   CROSS APPLY (SELECT t.c.value('let $n := . return count(/root/*[. << $n[1]])   2','INT') AS pos
         ) AS seq;

-- Test
SELECT * FROM  @tbl;

Output

 ---- ------- ------- 
| ID | descr | meter |
 ---- ------- ------- 
|  1 | desc1 |   100 |
|  2 | desc2 |   200 |
 ---- ------- -------