Separate string into columns

Question

Data :

Tree                 Depth
URL1||URL2             2
URL2||URL3             2    
URL3||URL4||URL5       3
URL1||URL2||URL3       3

In the above data the Tree column consists of string separated by "||". I need to convert the above data such that I have 3 columns (since the max depth is 3 in this example) , the result should look like:

COL1  COL2  COL3 DEPTH
URL1  URL2         2
URL2  URL3         2
URL3  URL4  URL5   3
URL1  URL2  URL3   3

In the above example the max depth is 3 however in real world it could be N number.

Answer 1

Good day,

In first glance it is look like we need to use user defined SPLIT function but since number of values that you have in each string is not more then 4, there is a much simpler and probably much better solution. We just need to use the built-in PARSENAME function.

I did not test the code but the solution should be something like this:

SELECT PARSENAME(REPLACE(Tree,'||','.'), 1) as col1, PARSENAME(REPLACE(Tree,'||','.'), 2) as col2, PARSENAME(REPLACE(Tree,'||','.'), 3) as col3, Depth
from TableName

• I replace the || with dot, since PARSENAME parse names that split by dot. this is the trick :-)

• I actually mentioned example like this in my lecture at the sqlsaturday #360. You can see the presentation. The lecture was about WHY to use SQLCLR, and not less important WHEN to use it over transact-SQL. but I also talked about when NOT to use it, and this was one of the examples there.

• In any case! if you are going to use SPLIT function then you should use SQLCLR and not T-SQL, as you can see here.

Answer 2

Try this, you just need to enter your Input Table, Output Table, Delimeter and Column to split. It can handle depth of more than 3, unlike PARSENAME function.

It is tested with 100,000 records and 30 split columns. It takes 10 sec to create the desired output.

Declare @Delimiter nvarchar(10) = '||'
Declare @InputTable nvarchar(2000) = '<<input table name>>'
Declare @OutputTable nvarchar(2000) = '<<output table name>>'
Declare @ColumnToSplit nvarchar(2000) = '<<column to split>>'


Declare @lsql nvarchar(max)
Declare @treeDepth int

If Object_id('dbo.treeDepth') is not null 
Drop table dbo.treeDepth

CREATE TABLE dbo.treeDepth (depth INT)

declare @ltext nvarchar(max)= 'Select max(1+(len('+@ColumnToSplit+')- len(Replace('+@ColumnToSplit+','''+@Delimiter+''','''')))/(len('''+@Delimiter+'''))) from '+@InputTable

insert dbo.treeDepth EXEC(@ltext)

Select @lsql = isnull(@lsql+',','') + 
'xmlname.value(''/Node[1]/Node['+cast(number+1 as nvarchar)+']'',''varchar(1000)'') AS Col_'+cast(number+1 as nvarchar)+''
from master..spt_values where type = 'P' and number < (Select * from dbo.treeDepth)

set @lsql = '
WITH ForXML
AS
(
    SELECT *,
    CONVERT(XML,''<Node><Node>''  
    + REPLACE('+@ColumnToSplit+','''+@Delimiter+''', ''</Node><Node>'') + ''</Node></Node>'') AS xmlname
      FROM '+@InputTable+'
)

Select *, '+@lsql+' Into '+@OutputTable+' From ForXML


Alter table '+@OutputTable+' 
Drop column xmlname
' 

EXEC(@lsql)