sql - Split the numeric string column in to multiple columns after each comma separated

Question

I need to convert this in to three columns, length is not static since above numbers are coming based on calculation.

Answer 1

In SQL Server 2016 you can use STRING_SPLIT to split the string (once you remove the surrounding ( and )) and then PIVOT that result to columns:

WITH CTE AS (
  SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
  FROM STRING_SPLIT(REPLACE(REPLACE('(0.0000, 3546.0000, 253422.000)', '(', ''), ')', ''), ',')
)
SELECT [1] AS Val1,
       [2] AS Val2,
       [3] AS Val3
FROM CTE
PIVOT (
  MAX(value)
  FOR rn IN ([1], [2], [3])
) p

Output:

Val1        Val2        Val3
0.0000      3546.0000   253422.000

Demo on dbfiddle

To do this with values in a table, you just need to CROSS APPLY the STRING_SPLIT to the table inside the CTE. For example, if the column is called exclusion in a table called data:

WITH CTE AS (
  SELECT exclusion, value, ROW_NUMBER() OVER (PARTITION BY exclusion ORDER BY (SELECT NULL)) AS rn
  FROM data
  CROSS APPLY STRING_SPLIT(exclusion, ',')
)
SELECT exclusion,
       [1] AS Val1,
       [2] AS Val2,
       [3] AS Val3
FROM CTE
PIVOT (
  MAX(value)
  FOR rn IN ([1], [2], [3])
) p

Demo on dbfiddle