How to split a column in SQL Server?

Today, one of my friend ask me: I have a column that contain different data and contains commas (delimiter character is comma), so could you split it into one specific column.

I want to show with picture what he wants.

He wanted to get second column, it means first data after first comma.

I show you how to transform this data in a test table.

create table ComplexData(
id int,
StringData varchar(250)

);
go

insert into ComplexData(id,StringData) values(1,'10000,Istanbul,20,0,Red')
insert into ComplexData(id,StringData) values(2,'30000,Ankara,30,1')
insert into ComplexData(id,StringData) values(3,'40000,Izmir,20,0,Red')
insert into ComplexData(id,StringData) values(4,'30000,Elazig,30,0')
insert into ComplexData(id,StringData) values(5,'10000,Bursa,20,0,Red')
insert into ComplexData(id,StringData) values(6,'30000,Adana,30,0')
insert into ComplexData(id,StringData) values(7,'40000,Kocaeli,20,1,Red')
insert into ComplexData(id,StringData) values(8,'30000,Ankara,40,1')
insert into ComplexData(id,StringData) values(9,'40000,Kocaeli,20,1,Red')
insert into ComplexData(id,StringData) values(10,'30000,Eskisehir,40,1,Blue')

After test data I should create a udf function.

-- =============================================
-- Author: Selcuk KILINC
-- Create date: 2021.10.08
-- Description: String Split
-- =============================================
CREATE or ALTER FUNCTION UDF_StringSplit
(
@stringParam varchar(250),
@splitChar varchar(2),
@whichSubStr int
)

RETURNS varchar(100)
AS
BEGIN
declare @tbl table(
id int identity(1,1),
SubStr varchar(100)
);

insert into @tbl(SubStr)
select value from string_split(@stringParam,@splitChar)

-- Return the result of the function
RETURN (select SubStr from @tbl where id=@whichSubStr)

END
GO

Then call the udf in the query:

select *,dbo.UDF_StringSplit(StringData,',',2) as City from ComplexData;

With the 3rd parameter you can split whatever you want. I tried to get second data from string above.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.