Using T-Sql, Return Nth Delimited Element from a String

Using T-SQL, return nth delimited element from a string

Here is my initial solution...
It is based on work by Aaron Bertrand http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

I simply changed the return type to make it a scalar function.

Example:
SELECT dbo.GetSplitString_CTE('1,222,2,67,888,1111',',',4)

CREATE FUNCTION dbo.GetSplitString_CTE
(
   @List       VARCHAR(MAX),
   @Delimiter  VARCHAR(255),
   @ElementNumber int
)
RETURNS VARCHAR(4000)
AS
BEGIN

   DECLARE @result varchar(4000)    
   DECLARE @Items TABLE ( position int IDENTITY PRIMARY KEY,
                          Item VARCHAR(4000)
                         )  

   DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);  

   WITH a AS
   (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, @ld), 0), @ll),
           [value] = SUBSTRING(@List, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, @ld), 0), @ll) - 1)
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + @ld,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll),
           [value] = SUBSTRING(@List, [end] + @ld, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll)-[end]-@ld)
       FROM a
       WHERE [end] < @ll
   )
   INSERT @Items SELECT [value]
   FROM a
   WHERE LEN([value]) > 0
   OPTION (MAXRECURSION 0);

   SELECT @result=Item
   FROM @Items
   WHERE position=@ElementNumber

   RETURN @result;
END
GO

Get nth word in a string delimited by "."

I'd suggest to use Common Table Expressions:

;WITH CTE AS
(
  SELECT 1 AS RowNo, LEFT(MyColumn, CHARINDEX('.', MyColumn)-1) AS Word,
    RIGHT(MyColumn, LEN(MyColumn) - CHARINDEX('.', MyColumn)) AS Remainder
  FROM Dummy
  WHERE CHARINDEX('.', MyColumn) >0
  UNION ALL
  SELECT RowNo +1, LEFT(Remainder, CHARINDEX('.', Remainder)-1) AS Word,
    RIGHT(Remainder, LEN(Remainder) - CHARINDEX('.', Remainder)) AS Remainder
  FROM CTE
  WHERE CHARINDEX('.', Remainder) >0
  UNION ALL
  SELECT RowNo+ 1, Remainder As Word, NULL As Remainder
  FROM CTE
  WHERE CHARINDEX('.', Remainder) = 0
)
SELECT Word
FROM CTE
WHERE RowNo = 3

Result:

X24

A12151

SQLFiddle

For further details, please see: Recursive Queries Using Common Table Expressions

[EDIT#2]

Assuming that you want to get every single word into different column, you have to change last SELECT statement to:

SELECT ID, [1], [2], [3], [4]
FROM (
  SELECT RowNo, Word, ROW_NUMBER() OVER(PARTITION BY RowNo ORDER BY RowNo) AS ID
  FROM CTE
) AS src
PIVOT (MAX(Word) FOR RowNo IN([1], [2], [3], [4])) AS pvt

Result:

ID  1       2           3       4

1   2345    35S982F     A12151  AB4R

2   STR4    Z2Z2        X24     (null)

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

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;

Extracting nth field of string delimited by ":" stored in a SQL column

Perhaps a little XML as the parser

Example

Select A.Format
      ,B.*
 From  YourTable A
 Cross Apply (
                Select Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(A.Format,':','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Returns

Format                  Pos2    Pos3    Pos4
GT:AD:DP:GQ:PL          AD      DP      GQ
GT:AD:DP:GQ:PL          AD      DP      GQ
GT:AD:DP:GQ:PL          AD      DP      GQ
GT:AD:DP:GQ:PL          AD      DP      GQ
GT:AD:DP:GQ:PL          AD      DP      GQ
GT:AD:DP:GQ:PGT:PID:PL  AD      DP      GQ
GT:AD:DP:GQ:PL          AD      DP      GQ
GT:AD:DP:GQ:PGT:PID:PL  AD      DP      GQ
GT:AD:DP:GQ:PGT:PID:PL  AD      DP      GQ
GT:AD:DP:GQ:PGT:PID:PL  AD      DP      GQ
GT:AD:DP:GQ:PGT:PID:PL  AD      DP      GQ

Or a Simple version

Select A.Format
      ,Pos2 = Cast('<x>' + replace(Format,':','</x><x>')+'</x>' as xml).value('/x[2]','varchar(max)')
      ,Pos3 = Cast('<x>' + replace(Format,':','</x><x>')+'</x>' as xml).value('/x[3]','varchar(max)')
      ,Pos4 = Cast('<x>' + replace(Format,':','</x><x>')+'</x>' as xml).value('/x[4]','varchar(max)')
 From  YourTable A

Or if Open to a UDF

Take a peek at TSQL/SQL Server - table function to parse/split delimited string to multiple/separate columns

EDIT - Update for Sample

Select A.Format
      ,GT = Cast('<x>' + replace(Sample,':','</x><x>')+'</x>' as xml).value('/x[1]','varchar(max)')
      ,AD = Cast('<x>' + replace(Sample,':','</x><x>')+'</x>' as xml).value('/x[2]','varchar(max)')
      ,DP = Cast('<x>' + replace(Sample,':','</x><x>')+'</x>' as xml).value('/x[3]','varchar(max)')
      ,GQ = Cast('<x>' + replace(Sample,':','</x><x>')+'</x>' as xml).value('/x[4]','varchar(max)')
 From  YourTable A

SQL Server - find nth occurrence in a string

One way (2k8);

select 'abc_1_2_3_4.gif  ' as img into #T
insert #T values ('zzz_12_3_3_45.gif')

;with T as (
    select 0 as row, charindex('_', img) pos, img from #T
    union all
    select pos + 1, charindex('_', img, pos + 1), img
    from T
    where pos > 0
)
select 
    img, pos 
from T 
where pos > 0   
order by img, pos

>>>>

img                 pos
abc_1_2_3_4.gif     4
abc_1_2_3_4.gif     6
abc_1_2_3_4.gif     8
abc_1_2_3_4.gif     10
zzz_12_3_3_45.gif   4
zzz_12_3_3_45.gif   7
zzz_12_3_3_45.gif   9
zzz_12_3_3_45.gif   11

Update

;with T(img, starts, pos) as (
    select img, 1, charindex('_', img) from #t
    union all
    select img, pos + 1, charindex('_', img, pos + 1)
    from t
    where pos > 0
)
select 
    *, substring(img, starts, case when pos > 0 then pos - starts else len(img) end) token
from T
order by img, starts

>>>

img                 starts  pos     token
abc_1_2_3_4.gif     1       4       abc
abc_1_2_3_4.gif     5       6       1
abc_1_2_3_4.gif     7       8       2
abc_1_2_3_4.gif     9       10      3
abc_1_2_3_4.gif     11      0       4.gif  
zzz_12_3_3_45.gif   1       4       zzz
zzz_12_3_3_45.gif   5       7       12
zzz_12_3_3_45.gif   8       9       3
zzz_12_3_3_45.gif   10      11      3
zzz_12_3_3_45.gif   12      0       45.gif

SQL split based on delimeter, keep only second element

SELECT RIGHT(ColName , LEN(ColName) - CHARINDEX('\', ColName) )
FROM TABLEName

OR

SELECT PARSENAME(REPLACE(ColName , '\' , '.'),1)
FROM TableName

---

Related Topics