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



20 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
  7. Packers and Movers Chennai Give Safe and Reliable ***Household Shifting Services in Chennai with Reasonable ###Packers and Movers Price Quotation. We Provide Household Shifting, Office Relocation, ✔✔✔Local and Domestic Transportation Services, Affordable and Reliable Shifting Service Charges @
    Packers And Movers Chennai

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Packers and Movers Bangalore as a Services providing company can make all the difference to your ###Home Relocation experience. Bangalore based Company which offers versatile solutions, Right team that easily reduce the stress associated with a ✔✔✔Household Shifting, ***Vehicle Transportation. we help things run smoothly and reduce breakages and offer you seamless, Affordable, Reliable Shifting Services, Compare Shifting Charges @
    Packers And Movers Bangalore

    ReplyDelete
  10. Hire Best Packers And Movers Mumbai for hassle-free Household Shifting, ***Office Relocation, ###Car Transporation, Loading Unloading, packing Unpacking at affordable ✔✔✔ Price Quotation. Top Rated, Safe and Secure Service Providers who can help you with 24x7 and make sure a Untroubled Relocation Services at Cheapest/Lowest Rate
    Packers And Movers Mumbai

    ReplyDelete
  11. Packers and Movers Pune Provide High Quality ***Household Shifting, Home/Office Relocation, Insurance, Packing, Loading, ###Car Transportation Service Pune and High experiences, Top Rated, Safe and Reliable, Best and Secure Packers and Movers Pune Team List. Get ✔✔✔ Affordable Rate Charts and Compare Quotation and Save Money and Time .
    Packers And Movers Pune

    ReplyDelete
  12. Get Shifting/Relocation Quotation from ###Packers and Movers Delhi. Packers and Movers Delhi 100% Affordable and Reliable ***Household Shifting Services. Compare Transportation Charges and Save Time, Verified and Trusted Packers and Movers in Delhi, Cheap and Safe Local, Domestic House Shifting @
    Packers And Movers Delhi

    ReplyDelete
  13. Certified Technicians We at SPOLU offer the best mobile phone repairing services for all branded smartphones & tablets, across India through our dedicated mobile phone repair service centers in Ghaziabad, Noida, Greater Noida, and Gurgaon.
    Doorstep Mobile Repair Service in Ghaziabad

    ReplyDelete
  14. Orel State University which is also known as Government State Educational Institution of Higher Education is identified as a prime higher education Institution in the metropolis of Orel. Orel State University was founded in 1931 as an industrial-Pedagogical Institute. In 1932 Orel Pedagogical University became Orel State University. Orel State University is unique in many aspects.
    To get Admission and more information-
    Orel State Medical University

    ReplyDelete
  15. Orel State University is unique in many ways. It is the only classical university Orel, a member of the Association of classical Russian universities. It is the only institution in the region, which is training on a wide range of areas and specialties of higher and postgraduate education, training, re-training (more than 300).
    To Get more information and Admission-
    Orel State Medical University

    ReplyDelete
  16. Orel State University is unique in many aspects. Orel State University is the only classical university who is the member of the Association of classical universities of the Russian Federation and the only educational institution in the region which provide training on a wide range of areas and specialties of higher and postgraduate education, training, retraining.

    Orel State Medical University

    ReplyDelete