Wednesday, August 1, 2012

Return nth index of a delimited string in TSQL

CREATE FUNCTION [dbo].[fn_SplitSelect] (@sep VARCHAR(32), @s VARCHAR(MAX), @index INT)

RETURNS NVARCHAR(MAX)
AS
    BEGIN
        DECLARE @xml XML
        SET @XML = N'' + REPLACE(@s, @sep, '') + ''
        
        DECLARE @ret NVARCHAR(MAX)

        SELECT @ret = Item
        FROM (
                    SELECT r.value('.','VARCHAR(5)') as Item, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rownum
                    FROM @xml.nodes('//root/r') AS RECORDS(r)
                    ) t
             WHERE t.rownum = @index
        

        RETURN @ret
    END



1 comment:

  1. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from Dot Net Training in Chennai. or learn thru Dot Net Training in Chennai. Nowadays Dot Net has tons of job opportunities on various vertical industry.
    or Javascript Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry.

    ReplyDelete