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

   DECLARE @result varchar(4000)    
                          Item VARCHAR(4000)

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

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

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

   RETURN @result;

SELECT [dbo].fn_String_SplitByDelimiter (

Will output

taken from the below url, just updated it to work on SQL2005


  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.

    1. Great Article Cyber Security Projects projects for cse Networking Security Projects JavaScript Training in Chennai JavaScript Training in Chennai The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

  2. Thanks for sharing this valuable information to our vision.
    happy wheels
    happy wheels

  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

  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

  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 |

  6. This blog is so nice to me. I will continue to come here again and again. Visit my link as well. Good luck obat aborsi cara menggugurkan kandungan cara menggugurkan kandungan obat penggugur kandungan tanda tanda kehamilan cara menggugurkan kandungan obat aborsi obat telat datang bulan

  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

  8. This comment has been removed by the author.

  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

  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

  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

  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

  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

  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

  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

  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

  17. I do consider all the ideas you have presented on your post. They're really convincing and will certainly work. @ Packers And Movers Bangalore

  18. The gaming ground simply received slightly sweeter with Triple the Thrill™ – Honey Hearts™! The Top Up characteristic is triggered during the base sport or free sport by six or extra scattered 카지노사이트 sunflower symbols, awarding three spins. This slot gets even better when the Honey Reel symbol is triggered on reels two, three and four, awarding one Divine Reel spin.