SQL Split from Multivalue

Question

Hello

I have field contains like this

vd:07/21/2024 19:47:13 07/21/2024 19:43:21 07/21/2024 19:20:59 07/21/2024 19:03:13 07/21/2024 19:15:35 07/21/2024 19:08:38 07/21/2024 19:27:32 07/21/2024 19:24:04, vn:10_JKT01 09_JKT02 06_JKT03 01_JKT04 05_JKT05 02_JKT06 08_JKT07 07_JKT08, vr:Success Success Success Success Success Success Success Success

Actually that value contains 3 pairs, so the real value is

VD VN VR

07/21/2024 19:47:13 10_JKT01 Success

07/21/2024 19:43:21 10_JKT02 Success

07/21/2024 19:20:59 10_JKT03 Success

07/21/2024 19:03:13 10_JKT04 Success

07/21/2024 19:15:35 10_JKT05 Success

07/21/2024 19:08:38 10_JKT06 Success

07/21/2024 19:27:32 10_JKT07 Success

07/21/2024 19:24:04 10_JKT08 Success

How we can make SQL query to get VD and VR by VN? Example i want yo get VD and VR for 10_JKT06.

Answer 1

This solution works on SQL 2022 and Azure SQL Database/Managed Instance:

DECLARE @Handian nvarchar(MAX) = 'vd:07/21/2024 19:47:13 07/21/2024 19:43:21 07/21/2024 19:20:59 07/21/2024 19:03:13 07/21/2024 19:15:35 07/21/2024 19:08:38 07/21/2024 19:27:32 07/21/2024 19:24:04, vn:10_JKT01 09_JKT02 06_JKT03 01_JKT04 05_JKT05 02_JKT06 08_JKT07 07_JKT08, vr:Success Success Success Success Success Success Success Success'
DECLARE @vd nvarchar(MAX),
         @vn nvarchar(MAX),
        @vr nvarchar(MAX)
SELECT @vd = MIN(CASE ordinal WHEN 1 THEN ltrim(value) END),
       @vn = MIN(CASE ordinal WHEN 2 THEN ltrim(value) END),
       @vr = MIN(CASE ordinal WHEN 3 THEN ltrim(value) END)
FROM   string_split(@Handian, ',', 1)
SELECT @vd = substring(@vd, 4, len(@vd)),
       @vn = substring(@vn, 4, len(@vn)),
       @vr = substring(@vr, 4, len(@vr))
DECLARE @values TABLE (id   int          NOT NULL,
                       vd   datetime2(0) NOT NULL,
                       vn   varchar(20)  NOT NULL,
                       vr   varchar(20)  NOT NULL)
; WITH vd AS (
   SELECT value, (ordinal+1)/2 AS ordinal
   FROM   string_split(@vd, ' ', 1)
), agg_vd AS (
   SELECT vd = string_agg(value, ' '), ordinal
   FROM   vd
   GROUP  BY ordinal
)
INSERT @values(id, vd, vn, vr)
   SELECT vd.ordinal, parse(vd.vd AS datetime2(0) USING 'en-us'), vn.value, vr.value
   FROM   agg_vd vd
   JOIN   string_split(@vn, ' ', 1) AS vn ON vd.ordinal = vn.ordinal
   JOIN   string_split(@vr, ' ', 1) AS vr ON vd.ordinal = vr.ordinal
SELECT * FROM @values

For older versions of SQL Server, see my article Arrays and Lists in SQL Server.