SQL function to split string into table

Sometime in SQL server we need to split a long character string....here I am writing function to split string.....with string and delimiter as parameter and it returns table of string as result.

Split Function

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[funSplit](     @sInputList VARCHAR(8000) -- List of delimited items   , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items ) RETURNS @List TABLE (item VARCHAR(8000)) BEGIN        DECLARE @sItem VARCHAR(8000)        WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0         BEGIN                SELECT                 @sItem = RTRIM(LTRIM(SUBSTRING(@sInputList, 1,                                          CHARINDEX(@sDelimiter,@sInputList,0)-1))),                 @sInputList = RTRIM(LTRIM(SUBSTRING(@sInputList,                                                 CHARINDEX(@sDelimiter,@sInputList,0)                                                 + LEN(@sDelimiter), LEN(@sInputList))))                IF LEN(@sItem) > 0                 INSERT INTO @List SELECT @sItem         END        IF LEN(@sInputList) > 0         INSERT INTO @List SELECT @sInputList -- Put the last item in                 RETURN END

Use this function as:

SELECT * FROM [funSplit] ('aaa,bbb,ccc,ddd,eee',',')

hope it helps...:)

Cheers!!!!!!!!!!!!!!!