MSSQL Split Function returns table data

As we know SPLIT() is very popular function in many languages but unfortunately SQL Server 2000, 2005, 2008 does not have function SPLIT(), they just support some basic functions such as :RTRIM, LTRIM,SUBSTRING,LEFT,RIGHT...And due to these function we can easily create UDF(User Defined Function) as SPLIT() which we would like to mention in this article.

About split string function please refer to this link which is posted by HoanHuynh for more detailed : MSSQL Trim Function
The limitation of this function is that it just supports delimiter with amount to be one.
Now, we would like to create a more advanced SPLIT function which is used to split any string containing delimiter with amount more than one.This function returns data with “Table” type.

For example :
Input    : a string like this ‘HoanHuynh,QuynhNguyen,4rapiddev.com’
Output :

SPLIT_FUNCTION_DATA_RESULT

The solution is that we create a UDF named fSplit as below :

CREATE FUNCTION dbo.fSplit
(
	@DATA VARCHAR(MAX),
	@delimiter VARCHAR(5)
)
RETURNS @tblReturn TABLE
(
	Id INT IDENTITY(1,1),
	VALUE nvarchar(1000)
)
AS
BEGIN
   DECLARE @delimiterIdx INT -- Index of delimiter in data
 
    SET @delimiterIdx  = Charindex(@delimiter,@DATA)
	While (@delimiterIdx > 0)
	BEGIN
		INSERT INTO @tblReturn(VALUE)
		SELECT  Val = LEFT(@DATA, @delimiterIdx - 1)
 
		SET @DATA = SUBSTRING(@DATA,@delimiterIdx + 1,Len(@DATA))
 
		SET @delimiterIdx  = Charindex(@delimiter,@DATA) -- get next index of delimiter
	END
 
	INSERT INTO @tblReturn (VALUE)
	SELECT Val = Ltrim(Rtrim(@DATA))
 
	RETURN
END

After create this function, execute the below SQL statement:

SELECT * FROM dbo.fSplit('HoanHuynh,QuynhNguyen,4rapiddev.com',',')

We get expected result as the above image  :).