Hi,
I would like to be able to write a SQL statement to separate the texts in a string which has a few delimiters into different columns.
For example, I would like to be able to write a SQL to convert: (delimiter !)
text1!text2!text3!text4!
into four different columns as text1 text2 text3 text4
The number of string between the delimiter is different from one string to another.
I sincerely appreciate your help in this matter.
Kind regards,
Bijan
You could use this:
SELECT value FROM STRING_SPLIT('text1!text2!text3!text4!', '!') WHERE LEN(Value) > 0;
Read the documentation:
STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs
and to put it in columns you can use unpivot. If you provide a ddl with sample data we can give you the code.
Thanks very much for the reply.
I will try that but I have to say this is the first time I see this function!
I will also read the link you have sent me.
Much appreciated.
Kind regards,
Bijan
What real world problem are you trying to solve?
The problem with @RogierPronk solution is that it outputs each element of the string to a new row. To get it back to a single row with multiple columns, you then have to crosstab and group or pivot the data.
Since you have a variable number of elements - this tends to lead to a dynamic pivot based solution.
Another option to splitting data into columns is using CHARINDEX. Here is an example that allows for up to 12 columns:
Declare @pString varchar(8000) = 'text1!text2!text3!text4!'
, @pDelimiter char(1) = '!';
Select InputString = @pString
, p01_pos = p01.pos
, p02_pos = p02.pos
, p03_pos = p03.pos
, p04_pos = p04.pos
, p05_pos = p05.pos
, p06_pos = p06.pos
, p07_pos = p07.pos
, p08_pos = p08.pos
, p09_pos = p09.pos
, p10_pos = p10.pos
, p11_pos = p11.pos
, p12_pos = p12.pos
, col_01 = ltrim(substring(v.inputString, 1, p01.pos - 2))
, col_02 = ltrim(substring(v.inputString, p01.pos, p02.pos - p01.pos - 1))
, col_03 = ltrim(substring(v.inputString, p02.pos, p03.pos - p02.pos - 1))
, col_04 = ltrim(substring(v.inputString, p03.pos, p04.pos - p03.pos - 1))
, col_05 = ltrim(substring(v.inputString, p04.pos, p05.pos - p04.pos - 1))
, col_06 = ltrim(substring(v.inputString, p05.pos, p06.pos - p05.pos - 1))
, col_07 = ltrim(substring(v.inputString, p06.pos, p07.pos - p06.pos - 1))
, col_08 = ltrim(substring(v.inputString, p07.pos, p08.pos - p07.pos - 1))
, col_09 = ltrim(substring(v.inputString, p08.pos, p09.pos - p08.pos - 1))
, col_10 = ltrim(substring(v.inputString, p09.pos, p10.pos - p09.pos - 1))
, col_11 = ltrim(substring(v.inputString, p10.pos, p11.pos - p10.pos - 1))
, col_12 = ltrim(substring(v.inputString, p11.pos, p12.pos - p11.pos - 1))
From (Values (concat(@pString, replicate(@pDelimiter, 12)))) As v(inputString)
Cross Apply (Values (charindex(@pDelimiter, v.inputString, 1) + 1)) As p01(pos)
Cross Apply (Values (charindex(@pDelimiter, v.inputString, p01.pos) + 1)) As p02(pos)
Cross Apply (Values (charindex(@pDelimiter, v.inputString, p02.pos) + 1)) As p03(pos)
Cross Apply (Values (charindex(@pDelimiter, v.inputString, p03.pos) + 1)) As p04(pos)
Cross Apply (Values (charindex(@pDelimiter, v.inputString, p04.pos) + 1)) As p05(pos)
Cross Apply (Values (charindex(@pDelimiter, v.inputString, p05.pos) + 1)) As p06(pos)
Cross Apply (Values (charindex(@pDelimiter, v.inputString, p06.pos) + 1)) As p07(pos)
Cross Apply (Values (charindex(@pDelimiter, v.inputString, p07.pos) + 1)) As p08(pos)
Cross Apply (Values (charindex(@pDelimiter, v.inputString, p08.pos) + 1)) As p09(pos)
Cross Apply (Values (charindex(@pDelimiter, v.inputString, p09.pos) + 1)) As p10(pos)
Cross Apply (Values (charindex(@pDelimiter, v.inputString, p10.pos) + 1)) As p11(pos)
Cross Apply (Values (charindex(@pDelimiter, v.inputString, p11.pos) + 1)) As p12(pos);
You can create this as a function - then just pass the string and delimiter in a CROSS APPLY to get the columns you want from the input string. It can be extended or shortened - as needed. Example call would be:
Select *
From yourTable yt
Cross Apply dbo.fnSplitStringToColumns(yt.DataColumn, '!') ss
declare @bjan table(id int identity(1,1), mango varchar(2500))
insert into @bjan
select 'text1!text2!text3!text4' union
select 'baja#beje#biji#bojo'
select *
from (
SELECT Item, concat('Col', ItemNumber) as ColumnName, id
FROM @bjan
cross apply DelimitedSplit8K(replace(mango,'#','!'), '!')
) a
PIVOT
(
max(Item)
FOR ColumnName IN ([Col1], [Col2], [Col3], [Col4])
) as pivotTable
DelimitedSplit8K being a function by @JeffModen
to do it dynamically to accommodate multi values
use sqlteam
go
declare @query varchar(max), @columns varchar(max)
create table #bjan (id int identity(1,1),
mango varchar(2500))
insert into #bjan
select 'text1!text2!text3!text4' union
select 'baja#beje#biji#bojo#buju'
create table #distilled(item varchar(150),
id int,
ColumnName varchar(150))
insert into #distilled
SELECT Item, id,
concat('Col', ItemNumber) as ColumnName
FROM #bjan
cross apply
DelimitedSplit8K(replace(mango,'#','!'), '!')
SELECT @columns =
LEFT( b.columnnames, LEN( b.columnnames ) - 1 )
FROM (
SELECT '[' + ColumnName + '], '
FROM (
SELECT distinct ColumnName
FROM #distilled
) a
ORDER BY a.ColumnName
FOR XML PATH ( '' )
) b ( columnnames );
select @query = '
select ' + @columns + '
from (
SELECT Item, id, ColumnName
FROM #distilled
) a
PIVOT
(
max(Item)
FOR ColumnName IN (' + @columns + ')
) as pivotTable '
exec(@query)
drop table #bjan
drop table #distilled
Thank you all for provision of solution to this problem.
I must admit, they are much more complicated than I expected. I was really expecting with the use of charindex function, LEFT, RIGHT and maybe some Substring functions to extract the required text.
Kind regards,
Bijan
Yes at first glance it might seem complicated. But functions such as DelimitedSplit8K string_split do all of that for you under the hood.