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’
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 :).