Separate data from string to get data in columns-CodePudding

I have to or more rows with data as one string. Below is example of two rows of data which I have

'Country: USA, Town: Washington, Street: North Wells Street, Post Code: 60614, ID: 1'

'Country: USA, Town: Chicago, Street: 26th Street NW, Post Code: 20037, ID: 2'

How to seperate strings in MS SQL to get result in table like below?

Country	Town	Street	Post Code	ID
USA	Washington	North Wells Street	60614	1
USA	Chicago	26th Street NW	20037	2

CodePudding user response：

You can use SPLIT_STRING to convert a single string into mutiple parts and then PIVOT (operator) to build columns from ROWs.

As an example :

WITH 
T0 AS 
(SELECT * 
 FROM (VALUES ('Country: USA, Town: Washington, Street: North Wells Street, Post Code: 60614, ID: 1'),
              ('Country: USA, Town: Chicago, Street: 26th Street NW, Post Code: 20037, ID: 2')) AS T (x)
),
T1 AS 
(
SELECT x, LTRIM(value) AS VAL, ROW_NUMBER() OVER(PARTITION BY x ORDER BY x ) AS RN
FROM   T0
CROSS APPLY STRING_SPLIT(T0.x, ',') 
)
SELECT x, 
       REPLACE([1], 'Country: ', '') AS COUNTRY, 
       REPLACE([2], 'Town: ', '') AS TOWN, 
       REPLACE([3], 'Street: ', '') AS STREET, 
       REPLACE([4], 'Post Code: ', '') AS POSTCODE, 
       REPLACE([5], 'ID: ', '') AS ID
FROM   T1
PIVOT (MAX(VAL)
       FOR RN IN ([1], [2], [3], [4], [5]) 
      ) AS PV;

Works on Microsoft SQL Server