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