Returning index of n-th element in string using tsql

How in SQL Server can I return index of n-th element in string. For example I have string: a,b,c,d,e Now I want to receive all elements, before third coma, so a,b,c. For doing this I need to have index of third coma. Charindex does not fit here, because I cannot pass number of coma, for which I want the index. Is there any built-in function which can do it ?

Update, @Andriy M.

Hey, the link You pointed me to is also useful. Below I've modified the shown sql a bit to get what I want:

CREATE FUNCTION [dbo].[nthindexsub](@String varchar(8000), @Delimiter char(1), @DelimitersCount int)       
 returns varchar(8000)
 as       
 begin       
     declare @Substring varchar(8000)
     declare @temptable TABLE (itemindex int identity(1,1), items varchar(8000))
     declare @idx int       
     declare @slice varchar(8000)     

     select @idx = 1       
         if len(@String)<1 or @String is null  return @Substring 

     while @idx!= 0       
     begin       
         set @idx = charindex(@Delimiter,@String)       
         if @idx!=0       
             set @slice = left(@String,@idx - 1)       
         else       
             set @slice = @String       

         if(len(@slice)>0)  
             insert into @temptable(Items) val开发者_如何学Goues(@slice)       

         set @String = right(@String,len(@String) - @idx)       
         if len(@String) = 0 break       
     end

     select @Substring = COALESCE(@Substring + ',', '') + items from @temptable where itemindex <= @DelimitersCount  
     return @Substring;
 end

usage:

select dbo.nthindexsub('a,b,c,d,e', ',', 3)

the result is: a,b,c

I'm not sure why you just want the index? why not just split them into rows, it is faster and better to work with rows. Here is a simple TSQL split example:

DECLARE @List varchar(500)
       ,@SplitOn char(1)
SELECT @List='aaa,bb,c,dd,eeee'
      ,@SplitOn=','
;WITH NumbersCTE AS
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number+1
        FROM NumbersCTE
        WHERE Number<LEN(@List)
)
, AllSplit AS
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY number) AS RowNumber
            ,LTRIM(RTRIM(SUBSTRING(ListValue, number+1, CHARINDEX(@SplitOn, ListValue, number+1)-number - 1))) AS ListValue
        FROM (
                 SELECT @SplitOn + @List + @SplitOn AS ListValue
             ) AS InnerQuery
            INNER JOIN NumbersCTE n ON n.Number < LEN(InnerQuery.ListValue)
        WHERE SUBSTRING(ListValue, number, 1) = @SplitOn
)
SELECT
    ListValue
    FROM AllSplit
    WHERE RowNumber<=3

OUTPUT

ListValue
----------------
aaa
bb
c

There are many ways to split string in SQL Server 2005. This article covers the PROs and CONs of just about every method:

"Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

You need to create a split function. This is how a split function can be used:

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server (in the previous example I use a CTE), see the previous link, which explains the PROs and CONs of each.

For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 10,000:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this split function:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO 

You can now easily split a CSV string into a table and join on it:

select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')

OUTPUT:

ListValue
-----------------------
1
2
3
4
5
6777

(6 row(s) affected)

Your can use the CSV string like this, not temp table necessary:

SELECT 
    y.* 
    FROM YourTable  y
        INNER JOIN dbo.FN_ListToTable(',',@MyList) x ON y.pk=x.ListValue
    Where y.aaa='5'

It is possible to do in TSQL, BUT TSQL is not good at (or designed for) string manipulation, and therefore might be slow if you call it often.

Might be better to create a CLR Stored Procedure.

if you want just want the first three elements, try nesting CHARINDEX like this:

DECLARE @YourTable table (csv varchar(50))
INSERT @YourTable VALUES ('aaa,bb,c,dd,eeee')
INSERT @YourTable VALUES ('aaa,bb,c,dd')
INSERT @YourTable VALUES ('aaa,bb,c')
INSERT @YourTable VALUES ('aaa,bb')
INSERT @YourTable VALUES ('aaa')
INSERT @YourTable VALUES (null)

SELECT 
    csv
        ,CHARINDEX(',',csv+',',CHARINDEX(',',csv+',',CHARINDEX(',',csv+',',0)+1)+1) AS ThirdComma
    FROM @YourTable
    WHERE CHARINDEX(',',csv,0)>0 
        AND CHARINDEX(',',csv+',',CHARINDEX(',',csv+',',CHARINDEX(',',csv+',',0)+1)+1)>0

OUTPUT:

csv                                                
-------------------------------------------------- -----------
aaa,bb,c,dd,eeee                                   9
aaa,bb,c,dd                                        9
aaa,bb,c                                           9

this will only return rows where there are three elements. if you want it when there are one, two, and/or three, try this:

DECLARE @YourTable table (csv varchar(50))
INSERT @YourTable VALUES ('aaa,bb,c,dd,eeee')
INSERT @YourTable VALUES ('aaa,bb,c,dd')
INSERT @YourTable VALUES ('aaa,bb,c')
INSERT @YourTable VALUES ('aaa,bb')
INSERT @YourTable VALUES ('aaa')
INSERT @YourTable VALUES (null)

;WITH FindThird AS
(
    SELECT csv, CHARINDEX(',',csv+',',CHARINDEX(',',csv+',',CHARINDEX(',',csv+',',0)+1)+1) AS ThirdComma
    FROM @YourTable
)
SELECT 
    csv
        ,CASE 
             WHEN ThirdComma IS NULL OR ThirdComma=0 THEN LEN(csv)+1
             ELSE ThirdComma
         END AS ThirdComma
    FROM FindThird

OUTPUT:

csv                 ThirdComma
------------------- -----------
aaa,bb,c,dd,eeee    9
aaa,bb,c,dd         9
aaa,bb,c            9
aaa,bb              7
aaa                 4
NULL                NULL