How to split a comma-separated value to columns
CREATE FUNCTION [dbo].[fn_split_string_to_column] (
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @out_put TABLE (
[column_id] INT IDENTITY(1, 1) NOT NULL,
[value] NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @value NVARCHAR(MAX),
@pos INT = 0,
@len INT = 0
SET @string = CASE
WHEN RIGHT(@string, 1) != @delimiter
THEN @string + @delimiter
ELSE @string
END
WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
BEGIN
SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
SET @value = SUBSTRING(@string, @pos, @len)
INSERT INTO @out_put ([value])
SELECT LTRIM(RTRIM(@value)) AS [column]
SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
END
RETURN
END
how to split the comma separated value into columns
first create function to split values
create function [dbo].[udf_splitstring] (@tokens varchar(max),
@delimiter varchar(5))
returns @split table (
token varchar(200) not null )
as
begin
declare @list xml
select @list = cast('<a>'
+ replace(@tokens, @delimiter, '</a><a>')
+ '</a>' as xml)
insert into @split
(token)
select ltrim(t.value('.', 'varchar(200)')) as data
from @list.nodes('/a') as x(t)
return
end
SELECT
max(CASE WHEN TOKEN='CLAR' THEN TOKEN END) 'NAME1' ,
max(CASE WHEN TOKEN='ALWIN' THEN TOKEN END) 'NAME2',
max(CASE WHEN TOKEN='ANTONY' THEN TOKEN END) 'NAME3',
max(CASE WHEN TOKEN='RINU' THEN TOKEN END) 'NAME4',
max(CASE WHEN TOKEN='DAMI' THEN TOKEN END) 'NAME5',
max(CASE WHEN TOKEN='PRINCE' THEN TOKEN END) 'NAME6'
FROM #Table1 as t1
CROSS APPLY [dbo].UDF_SPLITSTRING(name,',') as t2
output
NAME1 NAME2 NAME3 NAME4 NAME5 NAME6
clar alwin antony rinu dami prince
Split Comma Separated values into multiple column
Your sample data may not need any splitting. You want to move the data to a column based on the value it finds. You can do this a bit simpler than splitting the data. This works just fine for your sample data.
declare @Something table
(
Combined_Column varchar(10)
)
insert @Something values
('1,2,3')
, ('2')
, ('1,3')
, ('1,2,3,4')
, ('1,3,4')
, ('1')
, ('4')
select *
, col1 = case when charindex('1', s.Combined_Column) > 0 then 1 end
, col2 = case when charindex('2', s.Combined_Column) > 0 then 2 end
, col3 = case when charindex('3', s.Combined_Column) > 0 then 3 end
, col4 = case when charindex('4', s.Combined_Column) > 0 then 4 end
from @Something s
Split comma separated values into target table with fixed number of columns
It is typically bad design to store CSV values in a single column. If at all possible, use an array or a properly normalized design instead.
While stuck with your current situation ...
For known small maximum number of elements
A simple solution without trickery or recursion will do:
SELECT id, 1 AS rnk
, split_part(csv, ', ', 1) AS c1
, split_part(csv, ', ', 2) AS c2
, split_part(csv, ', ', 3) AS c3
, split_part(csv, ', ', 4) AS c4
, split_part(csv, ', ', 5) AS c5
FROM tbl
WHERE split_part(csv, ', ', 1) <> '' -- skip empty rows
UNION ALL
SELECT id, 2
, split_part(csv, ', ', 6)
, split_part(csv, ', ', 7)
, split_part(csv, ', ', 8)
, split_part(csv, ', ', 9)
, split_part(csv, ', ', 10)
FROM tbl
WHERE split_part(csv, ', ', 6) <> '' -- skip empty rows
-- three more blocks to cover a maximum "around 20"
ORDER BY id, rnk;
db<>fiddle here
id
being the PK of the original table.
This assumes ', ' as separator, obviously.
You can adapt easily.
Related:
- Split comma separated column data into additional columns
For unknown number of elements
Various ways. One way use regexp_replace()
to replace every fifth separator before unnesting ...
-- for any number of elements
SELECT t.id, c.rnk
, split_part(c.csv5, ', ', 1) AS c1
, split_part(c.csv5, ', ', 2) AS c2
, split_part(c.csv5, ', ', 3) AS c3
, split_part(c.csv5, ', ', 4) AS c4
, split_part(c.csv5, ', ', 5) AS c5
FROM tbl t
, unnest(string_to_array(regexp_replace(csv, '((?:.*?,){4}.*?),', '\1;', 'g'), '; ')) WITH ORDINALITY c(csv5, rnk)
ORDER BY t.id, c.rnk;
db<>fiddle here
This assumes that the chosen separator ;
never appears in your strings. (Just like ,
can never appear.)
The regular expression pattern is the key: '((?:.*?,){4}.*?),'
(?:)
... “non-capturing” set of parentheses()
... “capturing” set of parentheses*?
... non-greedy quantifier{4}?
... sequence of exactly 4 matches
The replacement '\1;'
contains the back-reference \1
.
'g'
as fourth function parameter is required for repeated replacement.
Further reading:
- PostgreSQL & regexp_split_to_array + unnest
- Apply `trim()` and `regexp_replace()` on text array
- PostgreSQL unnest() with element number
Other ways to solve this include a recursive CTE or a set-returning function ...
Fill from right to left
(Like you added in How to put values starting from the right side into columns?)
Simply count down numbers like:
SELECT t.id, c.rnk
, split_part(c.csv5, ', ', 5) AS c1
, split_part(c.csv5, ', ', 4) AS c2
, split_part(c.csv5, ', ', 3) AS c3
, split_part(c.csv5, ', ', 2) AS c4
, split_part(c.csv5, ', ', 1) AS c5
FROM ...
db<>fiddle here
SQL server split string into columns by delimiter (dynamic length)
An xml-based solution
declare @tmp table (STRING varchar(500))
insert into @tmp
values
('AA.0.HJ')
,('AABBCC.099.0')
,('0.91.JAH21')
;WITH Splitted
AS (
SELECT STRING
,CAST('<x>' + REPLACE(STRING, '.', '</x><x>') + '</x>' AS XML) AS Parts
FROM @tmp
)
SELECT STRING
,Parts.value(N'/x[1]', 'varchar(50)') AS [First]
,Parts.value(N'/x[2]', 'varchar(50)') AS [Second]
,Parts.value(N'/x[3]', 'varchar(50)') AS [Third]
FROM Splitted;
Output:
sql server split comma separated values into columns
Dynamically solve this problem, use DSQL to add more columns in the result accordingly.
--create split function
CREATE FUNCTION [dbo].[SO_Split]
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List =Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
--below is the dynamic solution for this problem
declare @sql nvarchar(3000) = 'select *'
declare @cnt int = 1
declare @rowNum int = (select max(a) from (select(select max(id) as id_max from dbo.so_split(mul_query,'*')) as a from #test) as b)
while(@cnt <= @rowNum)
begin
set @sql = @sql + N', ISNULL((select value from dbo.so_split(mul_query,''*'') where id = '+cast(@cnt as nvarchar(5))+N'),''1'')'
set @cnt = @cnt + 1
end
set @sql = @sql + N' from #test'
exec sp_executesql @sql
The result is attached below.
How to split more than one comma separated column as a separate row in SQL using CROSS APPLY
Since - according to your description - you used CROSS APPLY
and your query was successfully executed, this means you are using a SQL Server DB, not MY SQL. You can do two CROSS APPLY
to get your expected result. This will produce exactly the outcome you have shown in your question:
SELECT name, phone, value courses FROM
(SELECT name, value phone, courses
FROM tblName CROSS APPLY STRING_SPLIT(phones, ',')) x
CROSS APPLY STRING_SPLIT(courses, ',')
ORDER BY name, courses, phone;
You can verify this here: db<>fiddle
But this is very risky and you really should avoid such comma-separated contents in one column. I highly recommend to create separate columns for the different values in future.
Related Topics
Find the Date/Time a Table's Column Was Created
Listagg Query "Ora-00937: Not a Single-Group Group Function"
Do Clustered Index on a Column Guarantees Returning Sorted Rows According to That Column
Good Database and Structure to Store Synonyms
Introducing Foreign Key Constraint May Cause Cycles or Multiple Cascade Paths
SQL to Include Condition in Where If Not Null
Conditional Operator in SQL Where Clause
Restore SQL Server Database - Failed: 38(Reached the End of the File.)
Bulk Update Multiple Rows in Same Query Using Postgresql
How to Select a Column in SQL Server with a Special Character in the Column Name
In SQL, What Is the Letter After a Table Name in a Select Statement
Rollback Multiple SQL Update Queries in Ms Access
Rolling Sum Previous 3 Months SQL Server
Determine Contiguous Dates in SQL Gaps and Islands
Oracle 11G: Default to Static Value When Query Returns Nothing