Search code examples
sqlsql-serverparsingsplit

Split a delimited string twice into a two column table


I have an array with the following format:

  • Values are surrounded by double quotation marks ""
  • Columns are separated by commas ,
  • Rows are separated by semi-colons ;
  • Array is surrounded by curly brackets {}

For example, the following string...

{"73","2022-09-02 11:42:37.00";"118","2022-08-31 10:41:59.00";"123","2022-09-01 15:26:06.00"}

...would represent a two-column table with the values 73, 118, and 123 in the first column and 2022-09-02 11:42:37.00, 2022-08-31 10:41:59.00, and 2022-09-01 15:26:06.00 in the second column.

I would like to convert this array to a table in SQL.

After looking at several other similar questions, I found an answer that led me to this solution...

DECLARE @array varchar(max)

SET @array = '{"73","2022-09-02 11:42:37.00";"118","2022-08-31 10:41:59.00";"123","2022-09-01 15:26:06.00"}'

SET @array = REPLACE(REPLACE(@array, '{', ''), '}', '')

SELECT Min(CASE b.ordinal WHEN '1' THEN b.value END) 'ID',
       Min(CASE b.ordinal WHEN '2' THEN b.value END) 'Date'
FROM STRING_SPLIT(@array, ';', 1) a
CROSS APPLY STRING_SPLIT(a.value, ',', 1) b
GROUP BY a.ordinal

There is also this option...

DECLARE @array varchar(max)

SET @array = '{"73","2022-09-02 11:42:37.00";"118","2022-08-31 10:41:59.00";"123","2022-09-01 15:26:06.00"}'

SET @array = REPLACE(REPLACE(@array, '{', ''), '}', '')

SELECT PARSENAME(REPLACE(a.value, ',', '.'), 2) 'ID',
       PARSENAME(REPLACE(a.value, ',', '.'), 1) 'Date'
FROM STRING_SPLIT(@array, ';', 1) a

However, I'm fairly new to SQL Server, so I have some questions:

  1. Which solution is preferable and why? Or is there a better option besides these two? I've seen some people use XML but I know very little about XML and wasn't sure if that'd work with this implementation since both columns have a variable number of characters.
  2. Why does the PARSENAME function automatically remove the double quotes around the values?
  3. Will both of these options keep the values paired correctly? (e.g., "73" is always in the same row as "2022-09-02 11:42:37.00"?)
  4. Bonus Question: If this were to be a 3+ columned table, would one solution be better over another?

Solution

  • Here is one approach that uses string_split() and a bit of JSON

    Note: If you want a Pos3 (or more) ... Just follow the patterm. If there is no value, it will return NULL.

    Example

    Declare @S varchar(max) = '{"73","2022-09-02 11:42:37.00";"118","2022-08-31 10:41:59.00";"123","2022-09-01 15:26:06.00"}'
    
    Select ID   = JSON_VALUE(JS,'$[0]')
          ,Date = JSON_VALUE(JS,'$[1]')
     From  string_split(replace(replace(@S,'{',''),'}',''),';') A
     Cross Apply (values ( ('['+A.Value+']') )) B(JS)
    

    Results

    ID      Date
    73      2022-09-02 11:42:37.00
    118     2022-08-31 10:41:59.00
    123     2022-09-01 15:26:06.00