SQL

SQL Split Function

Tri Nguyen | June 05, 2011 | ADD COMMENT
The following is my simplified version of the SQL Split Function:
ALTER FUNCTION fnSplit
(
    @strInput nvarchar(1000),
    @delimiter nvarchar(1)
)

RETURNS @table table
(
    id int identity(1,1),
    value nvarchar(100)
)

AS

BEGIN

    While (Charindex(@delimiter,@strInput)>0)
    Begin
        Insert Into @table (value)
        Select
            value = ltrim(rtrim(Substring(@strInput,1,Charindex(@delimiter,@strInput)-1)))

        Set @strInput = Substring(@strInput,Charindex(@delimiter,@strInput)+1,len(@strInput))
    End
   
    Insert Into @table (value)
    Select value = ltrim(rtrim(@strInput))

    Return
END;

Usage

SELECT * 
FROM fnSplit('ASP.NET, PHP, SQL, MySQL, JQuery, Javascript, HTML',',')

Output

id          value                                                                                               
-----------------------
1           ASP.NET                                                                                             
2           PHP                                                                                                 
3           SQL                                                                                                 
4           MySQL                                                                                               
5           JQuery                                                                                              
6           Javascript                                                                                          
7           HTML