SQL split or substring column value

Question

I have an sql column and value/structure as per below:

ColumnA
ROOT/South America/Lima/Test/Test2

Running a select query I want to extract "Lima" As a column value. I couldn't get the split string to work, or substring.

Any thoughts?

Possible duplicate of [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) — Bùi Đức Khánh, Mar 13 '18 at 09:18
In the case where the depth of the path may not be fixed, do you view `Lima` as the third entry from the left, or the third entry from the right? — Tim Biegeleisen, Mar 13 '18 at 09:21
What version? 2016 supports STRING_SPLIT ( string , separator ) — paparazzo, Mar 13 '18 at 10:04

Shnugo · Answer 1 · 2018-03-14T10:57:36.960

This is my approach to get the nth part of any delimited string:

DECLARE @mockupTable TABLE(ID INT IDENTITY, YourColumn VARCHAR(1000));

INSERT INTO @mockupTable VALUES('ROOT/South America/Lima/Test/Test2')
                              ,('Too/short')
                              ,('Three/parts/valid');

--The splitting is a one-liner:

SELECT *
      ,CAST('<x>' + REPLACE(YourColumn,'/','</x><x>') + '</x>' AS XML).value('/x[3]','nvarchar(max)') AS ThirdPart
FROM @mockupTable;

If your delimited strings might include XML-forbidden characters (namely &, < and >, you'd have to escape them (but that's easy):

Just use this instead

      ,CAST('<x>' + REPLACE((SELECT YourColumn [*] FOR XML PATH('')),'/','</x><x>') + '</x>' AS XML).value('/x[3]','nvarchar(max)') AS ThirdPart

Some explanation

The replacements of your delimiter / with </x><x> allow to get an XML like string, which can be casted to

<x>ROOT</x>
<x>South America</x>
<x>Lima</x>
<x>Test</x>
<x>Test2</x>

The XML's method .value() allows to use XQuery to get the third <x>. One advantage: If there is no third element, this won't break, just return NULL.

paparazzo · Answer 2 · 2018-03-13T11:32:43.250

A little out there but it works. Based on a recursive cte. You can set the delimiter, start and end.

declare @T table (iden int identity, col1 varchar(100));
insert into @T(col1) values
       ('ROOT/South America/Lima/Test/Test2')
     , ('ROOT/South America/Peru/Test/Test2')
     , ('ROOT/South America/Venuzuala')
     , ('ROOT/South America/'); 
declare @split char(1) = '/';
declare @start int = 2;
declare @end int = 3
select @split, @start, @end;
with cte as 
(  select t.iden, t.col1, charindex(@split, t.col1) as pos    , 1 as cnt
   from @T t
   union all 
   select t.iden, t.col1, charindex(@split, t.col1, t.pos + 1), cnt + 1 
   from cte t 
   where charindex(@split, t.col1, t.pos + 1) > 0 
     and cnt+1 <= @end
)
--select * from cte order by iden, cnt;
select --t1.*, t2.*, 
       SUBSTRING(t1.col1, t1.pos+1, t2.pos-t1.pos-1) as bingo
from cte t1 
join cte t2 
  on t2.iden = t1.iden 
 and t1.cnt = @start 
 and t2.cnt = @end
order by t1.iden;

score 0 · Answer 3 · answered May 19 '20 at 10:48

declare @t varchar(max) = 'ROOT/South America/Lima/Test/Test2'
select * from (
select 
[value]
,ROW_NUMBER() Over (Order by (select null )) [Level]
from string_split( @t , '/')
) d
where d.Level = 3

the code above dose the same with much simpler logic, split the text by '/' results to rows "String_Split", then row_number() to get the order of the results, each number is a level if no sorting has been done in the order by statment "(select null)" as a result by adding the level number in the where statment will get the level value we want, above will show level 3

SQL split or substring column value

3 Answers3

Some explanation