Click here to Skip to main content
Click here to Skip to main content

Split function

, 5 Jun 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Split User Defined Function, MS SQL Server 2008

This is MS SQL Server 2008 adopted Split function (UDF)

Source: Best Split UDF function with delimeter in T-SQL to return result as a table

 
--DROP Function [dbo].[fnStringSplitter]
CREATE Function [dbo].[fnStringSplitter]
(   
    @IDs Varchar(max) --A big string which may have delimeter in it or not
    ,@Delimiter Varchar(1) -- Delimeter to use for splitting up the given string
)
/*********************** RETURN *********************/
--Returns the table with specific values in a temporary table. Useful especially if you have any IDs in the
--given string and want to get them as a table row values.
--It is handy for multi selection parameters in SQL Server Reporting Services 
-- Example:
--@IDs = 1,2,3,4,5,6,7,8,9,10
--@Delimeter = ','
--Returns @Tbl_IDS, which is having 10 rows with above IDS in each row by splitting up with given delimeter [in this example ',']
/****************************************************/
Returns @Tbl_IDs Table (ID Varchar(500)) As
Begin
 
    -- Append comma
    Set @IDs = @IDs + @Delimiter
 
    -- Indexes to keep the position of searching
    Declare @Pos1 Int
    Declare @pos2 Int
    Declare @RowNum Int
 
    -- Start from first character
    Set @Pos1 = CharIndex(@Delimiter,@IDs,1)
    Set @Pos2=1
    While @Pos1 > 0
    Begin
        Insert @Tbl_IDs Values (Substring(@IDs,@Pos2,@Pos1-@Pos2))
        -- Go to next non comma character
        Set @Pos2=@Pos1+1
		-- Search from the next charcater
        Set @Pos1 = CharIndex(@Delimiter,@IDs,@Pos1+1)
    End
    Return
End
 

Test

 
SELECT * from dbo.fnStringSplitter(',,1,2,3,4,5,6,7,8,9,10', ',')

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

db_developer
Database Developer Freelancer
Ukraine Ukraine
MS SQL Server Database Developer with 7+ years experience
 
Technologies/languages: Business Intelligence, SQL, MDX, VBA, SQL Server, Analysis Services (SSAS), Reporting services (SSRS), Integration Services (SSIS), DataWarehouse.
Also: economic background.
 
Feel free to contact me for rates and details.

Comments and Discussions

 
QuestionMessage Automatically Removed Pinmemberrba12321-May-14 21:18 
SuggestionSplit function Pinmemberfregatepallada10-Jul-12 18:28 
Generalneat PinmemberNiklas Lindquist6-Jun-10 12:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150327.1 | Last Updated 5 Jun 2010
Article Copyright 2010 by db_developer
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid