Parsing attribute value pairs in T-SQL

Question

I have T-SQL records of the form {taskId}={owner} that I want to seperate into records of taskId's and owner's (in seperate fields). I found a similar problem's solution inspiring this code that seems 95% of the way there

SELECT [taskId]= LEFT(s.owner, ca.pos - 1),
       [owner] = SUBSTRING(s.owner, ca.pos + 1, 888000)
FROM (SELECT [GROUP_TASK_IDS] from [mydb][MyGroupTasks) s
CROSS APPLY (VALUES(CHARINDEX('=', s.owner))) ca(pos)

The nested (SELECT...) s is returning the records expected so I thought CROSS APPLY provides a join-like mechanism to apply the function to each record in the recordset that would then be given to the outer SELECT. What am I misunderstanding about CROSS APPLY and how do I correct my usage to return a table of taskId's and owner's from the SELECT'ed records of strings of the form {taskId}={owner}?

Please tag what version of SQL Server you're using (assuming it is such). — squillman, Oct 13 '21 at 20:57
I believe it is SQL Server 2012. We are moving to 2016. Each row of the input data in Group_Task_IDs (field case-insensitive in my Microsoft SQL Server Studio) looks like 12345=Joe Cool — Dr Dave, Oct 14 '21 at 12:03

Razvan Socol · Accepted Answer · 2021-10-20T18:23:44.980

I am assuming the following sample data:

CREATE TABLE MyGroupTasks ([GROUP_TASK_IDS] VARCHAR(100) PRIMARY KEY)

INSERT INTO dbo.MyGroupTasks (GROUP_TASK_IDS)
VALUES ('{123}={456}'),('{a}={bc}')

In this case, I have added just AS owner in your query to make it valid:

SELECT [taskId]= LEFT(s.owner, ca.pos - 1),
       [owner] = SUBSTRING(s.owner, ca.pos + 1, 888000)
FROM (SELECT [GROUP_TASK_IDS] AS owner from MyGroupTasks) s
CROSS APPLY (VALUES(CHARINDEX('=', s.owner))) ca(pos)

And it produces the following results:

taskId                         owner
------------------------------ ------------------------------
{123}                          {456}
{a}                            {bc}
(2 rows affected)

If you wanted these results:

taskId                         owner
------------------------------ ------------------------------
123                            456
a                              bc
(2 rows affected)

Change your query this way:

SELECT [taskId]= SUBSTRING(s.owner, 2, ca.pos - 3),
       [owner] = SUBSTRING(s.owner, ca.pos + 2, LEN(s.owner)-ca.pos-2)
FROM (SELECT [GROUP_TASK_IDS] AS owner from MyGroupTasks) s
CROSS APPLY (VALUES(CHARINDEX('=', s.owner))) ca(pos)

Later edit

CROSS APPLY is normally used to execute another query based on some criteria from the tables which are used before. See https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/ for an introduction in the typical usage of this clause. However, in the CROSS APPLY you can also use a VALUES clause (instead of a SELECT subquery). ca(pos) are the aliases assigned to the subquery and to the resulting column.

A similar query (with a more usual syntax) would be:

SELECT [taskId]= LEFT(s.owner, ca.pos - 1),
       [owner] = SUBSTRING(s.owner, ca.pos + 1, 888000)
FROM (SELECT [GROUP_TASK_IDS] AS owner from MyGroupTasks) s
CROSS APPLY (SELECT CHARINDEX('=', s.owner) AS pos) ca

Another way to write this (even more usual) would be:

SELECT LEFT(s.owner, s.pos - 1) AS taskId,
       SUBSTRING(s.owner, s.pos + 1, 888000) AS owner
FROM (
    SELECT  GROUP_TASK_IDS AS owner, 
            CHARINDEX('=', GROUP_TASK_IDS) AS pos
    FROM MyGroupTasks
) s

I apologize. I used braces to indicate variable data of that form, so the braces are not necessary in the data but I will try this solution shortly and credit the answer. And please explain exactly what the CROSS APPLY and ca(pos) does in "CROSS APPLY (VALUES(CHARINDEX('=', s.owner))) ca(pos)" — Dr Dave, Oct 18 '21 at 17:35

Parsing attribute value pairs in T-SQL

1 Answers1