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.