How to select the nth item in a field with separated values in a SQL database table?

Question

So, we got a code field with separated values like 'a_bb_ccc_dddd' and need the third value, that's 'ccc'.

I actually get the first with top N.

DECLARE @table1 TABLE (path VARCHAR(MAX));

INSERT INTO @table1 (path) 
VALUES ('a_bb_ccc_dddd'), ('111_222_333_444'), ('')

SELECT
    path, 
    (SELECT TOP 1 value 
     FROM STRING_SPLIT (path, '_')) AS part
FROM
    @table1

Possible duplicate of [SQL Server - find nth occurrence in a string](https://stackoverflow.com/questions/8726111/sql-server-find-nth-occurrence-in-a-string) — Thom A, Oct 07 '18 at 11:22
(Also [`DelimitedSplit8k_lead`](http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/)) — Thom A, Oct 07 '18 at 11:33
https://portosql.wordpress.com/2019/04/21/geracao-de-sequencias-numericas/ — José Diz, Sep 16 '19 at 15:06

score 2 · Accepted Answer · answered Oct 07 '18 at 12:18

2

How about this:

SELECT path, value
FROM @table1
OUTER APPLY (
  SELECT value
  FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), value 
    FROM string_split(path, '_')
    ) split (rn, value)
  WHERE rn = 3
) splitval;

answered Oct 07 '18 at 12:18

Wolfgang Kais

4,010
2
10
17

Thanks for your reply @wolfgang, great abstractionn, you nailed! – Santi Herranz Oct 07 '18 at 14:34
1. Split the string field path 2. Add row position of splitted with ROW_NUMBER(), nice trick! 3. Filter the position requested – Santi Herranz Oct 07 '18 at 17:10

score 1 · Answer 2 · answered Oct 07 '18 at 12:00

use this

create FUNCTION [dbo].[iteration] (@string nvarchar(max), @delimiter nvarchar(50),@iter int)

returns nvarchar(max)
as begin
declare @result nvarchar(300)
SELECT @result = value from --into #COLUMNs
(select value,row_number() over(order by (select null))  [rn]
FROM STRING_SPLIT(@string,'_'))a
where a.rn = @iter


return @result
end
GO

then simply run the query:

DECLARE @table1 TABLE (path varchar(max));
declare @iter int 
INSERT INTO @table1 (path) VALUES ('a_bb_ccc_dddd')
INSERT INTO @table1 (path) VALUES ('111_222_333_444')
INSERT INTO @table1 (path) VALUES ('')

select path, dbo.iteration(path,'_',3)
from @table1 a

if you want to change it to the 2nd iteration change the 3 to a 2 etc.

score 0 · Answer 3 · answered Oct 07 '18 at 11:22

This is tricky. You can probably easily find a split() function on the web that includes an ordinal number for the element.

Another method uses a recursive CTE:

with cte as (
      select stuff(path, 1, len(v.element) + 1, '') as path, v.element, 1 as lev
      from table1 cross apply
           (values (left(path, charindex('_', path + '_') - 1))) v(element)
      union all
      select stuff(cte.path, 1, len(v.element) + 1, ''), left(path, charindex('_', path + '_') - 1) as element, 1 + lev
      from cte cross apply
           (values (left(path, charindex('_', path + '_') - 1))) v(element)
      where path <> ''
     )
select *
from cte
where lev = 3;

score 0 · Answer 4 · answered Oct 07 '18 at 12:25

If you always want the third value, you could use this :

SELECT SUBSTRING(path, LPart + 1, LEN(path) - (LPart+RPart) ) Path
FROM(
SELECT 
    path 
,   LEN(LEFT(path,CHARINDEX('_', path, CHARINDEX('_', path) + 1))) LPart
,   LEN(SUBSTRING(path, CHARINDEX('_', path, CHARINDEX('_', path, CHARINDEX('_', path) + 1) + 1), LEN(path) - CHARINDEX('_', path, CHARINDEX('_', path, CHARINDEX('_', path) + 1) + 1) +1)) RPart 
from @table1
) D

You can use CHARINDEX('_', path) to get the position number of the first underscore, and then you can replicate it + 1 to get the second underscore CHARINDEX('_', path, CHARINDEX('_', path) + 1). Then, all you need to do is just use those numbers with the SUBSTRING().

How to select the nth item in a field with separated values in a SQL database table?

4 Answers4