Click here to Skip to main content
12,948,021 members (85,090 online)
Click here to Skip to main content
Add your own
alternative version


3 bookmarked
Posted 5 Jun 2010

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
    -- 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
        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)


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


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


About the Author

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.

You may also be interested in...

Comments and Discussions

QuestionMessage Automatically Removed Pin
21-May-14 20:18
memberrba12321-May-14 20:18 
SuggestionSplit function Pin
fregatepallada10-Jul-12 17:28
memberfregatepallada10-Jul-12 17:28 
Generalneat Pin
Niklas Lindquist6-Jun-10 11:33
memberNiklas Lindquist6-Jun-10 11:33 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170518.1 | Last Updated 5 Jun 2010
Article Copyright 2010 by db_developer
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid