SQLTeam.com | Weblogs | Forums

Separation of text

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 !)
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,


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,


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
    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

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
    FOR ColumnName IN (' + @columns + ')
 ) as pivotTable '


drop table #bjan
drop table #distilled
1 Like

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,


Yes at first glance it might seem complicated. But functions such as DelimitedSplit8K string_split do all of that for you under the hood.