Friday, January 10, 2014

T-SQL: Return nth delimited element from a string


CREATE FUNCTION [dbo].[fn_String_SplitByDelimiter]
(
   @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 , @ld INT;  
   set @ll =  LEN(@List) + 1
   set @ld = 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

Example
SELECT [dbo].fn_String_SplitByDelimiter (
   '1ST\2ND\3RD\4TH'
  ,'\'
  ,3)

Will output
3RD

taken from the below url, just updated it to work on SQL2005
http://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string



7 comments:

  1. There is a problem if there is an empty item in the list
    e.g. if item 5 is empty then item 6 is returned as item 5 etc.

    ReplyDelete
  2. Thanks for sharing this valuable information to our vision.
    Signature:
    happy wheels
    friv
    girlsgogames
    games2girls
    happy wheels

    ReplyDelete
  3. I would like more information about this, because it is very nice. Thanks for sharing!
    baixar facebook gratis
    baixar whatsapp gratis
    temple run 2
    traffic rider

    ReplyDelete
  4. Run 3 unblocked is the third install of the Run Serie. Skate, float, bounce, and/or jump your way through even more space tunnels! Run 2 unblocked game is next games in run series, now you can play run game with more and more level. Try to run as far as possible, good luck. Tanks Trouble is a game designed by danish Mads Purup. It is a maze party game with 1-3 players on one computer. The objective of the game is to destroy the other tanks to get a point.
    Run 2 - Run 2 unblocked - Tank Trouble - Tank Trouble 2

    ReplyDelete
  5. The blog or and best that is extremely useful to keep I can share the ideas. Age Of War 2
    Big Farm | Slitherio | Tank Trouble
    Of the future as this is really what I was looking for, I am very comfortable and pleased to come here. Thank you very much.
    Happy Wheels | Goodgeme Empire | Slither.io

    ReplyDelete
  6. This blog is so nice to me. I will continue to come here again and again. Visit my link as well. Good luck
    http://www.jualobataborsiherbal.com/ obat aborsi
    http://caramenggugurkankandungan.info/ cara menggugurkan kandungan
    http://www.jualobataborsiherbal.com/cara-menggugurkan-kandungan/ cara menggugurkan kandungan
    http://jualobatpenggugurkandungan.net/ obat penggugur kandungan
    http://tandatandakehamilan.net/ tanda tanda kehamilan
    http://tandatandakehamilan.net/cara-cepat-dan-selamat-menggugurkan-kandungan/ cara menggugurkan kandungan
    http://obataborsi59.com/ obat aborsi
    http://obattelatdatangbulan.info/ obat telat datang bulan

    ReplyDelete