Parsing attribute value pairs in T-SQL-CodePudding

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}?

CodePudding user response：

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)