locked
How to split a string using sql RRS feed

  • Question

  • Hi All,

    I've to write a query where I need to split a string, how this can be done? So let's say a column has value as 1,2,3,4,5 and I want to split this string and output it as:

    1

    2

    3

    4

    5

    Please advise.

    Saturday, September 27, 2014 5:21 PM

Answers

  • This article on my web site has (too) many methods for the purpose:
    http://www.sommarskog.se/arrays-in-sql-2005.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, September 27, 2014 5:29 PM
  • refer below articles.

    How to Convert or Split a Delimited String Values to Rows using T-SQL

    http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Saturday, September 27, 2014 5:30 PM
  • Lots of articles:

    Snap this user defined function too:

    CREATE FUNCTION [dbo].[ufn_SplitString_Separator](@InputStr VARCHAR(MAX), @Separator VARCHAR(1))
    RETURNS @tmpTable TABLE (OutputStr VARCHAR(MAX))
    AS BEGIN
    
    DECLARE @TmpPOS integer
    SET @TmpPOS = CHARINDEX(@Separator,@InputStr)
    
    WHILE @TmpPos > 0 BEGIN
    IF @TmpPos > 0 BEGIN
    	INSERT INTO @tmpTable VALUES (LTRIM(RTRIM(SUBSTRING(@InputStr,1,@TmpPos-1))))
    	SET @InputStr = SUBSTRING(@InputStr, @TmpPOS + 1, LEN(@InputStr) - @TmpPos)
    	SET @TmpPOS = CHARINDEX(@Separator,@InputStr)
    END ELSE BEGIN
    	INSERT INTO @tmpTable VALUES (LTRIM(RTRIM(@InputStr)))
    	SET @TmpPos = 0
    END
    END
    
    IF LEN(@InputStr) >  0 BEGIN
    	INSERT INTO @tmpTable VALUES (LTRIM(RTRIM(@InputStr)))
    END
    
    RETURN 
    END
    
    GO
    
    

    And you can use like this:


    SELECT * FROM DBO.[ufn_SplitString_Separator]('1,2,3,4,5',',')


    "If there's nothing wrong with me, maybe there's something wrong with the universe!"

    Saturday, September 27, 2014 5:42 PM

All replies