Extract number from string in SQL Server-CodePudding

I have a table with a column that is including different remarks of an specific process.

Sometimes there is 1 remark, and sometimes 4 different remarks.

It looks something like this :

Remarks 
|180|
|180|
|180|
|180|360|
|180|
|180|
|180|
|180|360|

I need to have the remarks in different columns.

I'm looking specific in a few remarks so I can create a case per each column.

Could you advice how to extract or looked for the number into the tuple ?

CodePudding user response：

In SQL Server 2016 and up:

;WITH shattered AS 
(
  SELECT t.Remarks, j.[key], j.value
    FROM dbo.TheTable AS t
    CROSS APPLY 
      OPENJSON('["'   REPLACE(t.Remarks, '|', '","')   '"]') AS j
    WHERE j.value > ''
)
SELECT Remarks, 
       Remark1 = [1], 
       Remark2 = [2], 
       Remark3 = [3], 
       Remark4 = [4]
FROM shattered 
PIVOT (MAX(value) FOR [key] IN ([1],[2],[3],[4])) AS p;

Example db<>fiddle

CodePudding user response：

You can use STRING_SPLIT and PIVOT on modern versions of SQL Server

SELECT
  Remarks.*
FROM YourTable t
CROSS APPLY (
    SELECT *
    FROM (
        SELECT
          s.value,
          rn = CONCAT('Remarks', ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))
        FROM STRING_SPLIT(t.Remarks, '|') s
        WHERE s.value <> ''
    ) s
    PIVOT (
        MAX(value) FOR (rn IN
            (Remarks1, Remarks2, Remarks3, Remarks4)
    ) p
) Remarks;