I have a problem splitting this column based on (*)

I need an output like;

Yes there is also a random I before (a) but hoping we can disregard somehow...

CodePudding user response：

Assuming there is something unique to identify each row, you can PIVOT based on a CROSS APPLY with STRING_SPLIT():

DECLARE @x table
(
  [Id] int IDENTITY(1,1),
  [Values] nvarchar(4000) -- this is a terrible column name
);

INSERT @x([Values]) VALUES
  (N'I (a)Some Text (b)Some more Text (c) Text'),
  (N'nonsense (a) mort (b) splungy (c) blat');

;WITH x AS 
(
  SELECT Id, [value] = N'('   f.[value], 
    rn = ROW_NUMBER() OVER (PARTITION BY x.Id ORDER BY f.[value])
  FROM @x AS x
  CROSS APPLY STRING_SPLIT(x.[Values], N'(') AS f
  WHERE f.[value] LIKE N'_)%'
)
SELECT [1], [2], [3] FROM x
PIVOT (MAX(value) FOR rn IN ([1],[2],[3])) AS p;

Results:

1               2                   3
------------    -----------------   --------
(a)Some Text    (b)Some more Text   (c) Text
(a) mort        (b) splungy         (c) blat

db<>fiddle

And here's a slightly different one that returns the Id and the exact column headers in the question.