How to split comma separated string values into columns?
Suppose you have a table, and one column of this table contains a comma separated string values and you are asked to split these comma separated string values into separate columns, means if string consists five sub string values which are separated by comma then you have to split this values into five columns based on their order.
Lets create a sample table named PhoneNumberList and insert some records into this table as shown below.
CREATE TABLE dbo.PhoneNumberList
(EmpId INT IDENTITY(1,1),
EmpPhoneNo VARCHAR(100)
)
INSERT INTO dbo.PhoneNumberList
(EmpPhoneNo)
VALUES
(‘7898798797,8989893333,1212121213,4545454545’),
(‘1313131345,4567676767’),
(‘4746464646,9898989898,8900000000,3434343434’),
(‘7878787878’)
As you can see below sample table named EmpPhone .
Now the requirement is that you have to split these comma separated phone number into columns.
You can see the maximum phone number that any employee is having that is four, so you will have maximum four columns that is used to split the comma separated phone number list into these columns as shown in below sample output that you need to implement.
Columns phn1, phn2, phn3, phn4 will be having a phone number values.
Lets split the comma separated phone number list into columns, For this we will use Cross Apply operator, String_Split function and SQL pivot.
Following query is used for splitting a comma separated phone number list into columns.
SELECT EmpId, EmpPhoneNo, ISNULL([phn1],'') AS [phn1], ISNULL([phn2],'') AS [phn2], ISNULL([phn3],'') AS [phn3], ISNULL([phn4],'') AS [phn4] FROM ( SELECT EmpId, EmpPhoneNo, 'Phn'+ CAST(ROW_NUMBER()OVER(PARTITION BY EmpId ORDER BY EmpId) AS VARCHAR) AS Col, Split.value FROM dbo.PhoneNumberList AS Emp CROSS APPLY String_split(EmpPhoneNo,',') AS Split ) AS tbl Pivot (Max(Value) FOR Col IN ([phn1],[phn2],[phn3],[phn4]) ) AS Pvt
As you can see, now the comma separated phone number string is split into separated columns.
Also Read..
