I have an array with the following format:
""
,
;
{}
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:
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