Enables SQL Server to perform the Split Function in stored procedures/views/functions
code: Can't Copy and Paste this? Click here for a copy-and-paste friendly version of this code!
Terms of Agreement:
By using this code, you agree to the following terms...
You may use
this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
You may link to this code from another website, but ONLY if it is not wrapped in a frame.
You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
--**************************************
-- Name: Split Function
-- Description:Enables SQL Server to perform the Split Function in stored procedures/views/functions
-- By: GuyH
--
-- Inputs:A STRING that you would like to split down into individual elements, based on the DELIMITER specified
--
-- Returns:a table with a row for each item found between the delimiter you specify
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=653&lngWId=5--for details.--**************************************
CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL ZERO FIRST TIME IN LOOP
SELECT @INDEX = 1
-- following line added 10/06/04 as null values cause issues
IF @String IS NULL RETURN
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Items) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END
Report Bad Submission
Your Vote
Other User Comments
4/4/2003 8:49:40 AM:
looks interesting, thanks. (If this comment was disrespectful, please report it.)
4/9/2003 12:26:35 AM:
I'm waiting for it. 5 Globes (If this comment was disrespectful, please report it.)
2/5/2004 8:08:51 AM:
You're a STUD, thanks!!! (If this comment was disrespectful, please report it.)
Id int identity(1,1), Value nvarchar(100) ) AS BEGIN
While (Charindex(@SplitOn,@List)>0) Begin
Insert Into @RtnValue (value) Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List)) End
Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List))
Return END (If this comment was disrespectful, please report it.)
6/10/2004 4:34:25 AM:
Dudes I had a problem with this Split function: when @String is NULL in this function, SQL Server goes berserk! CPU level goed to 99% on my machine. Try this for yourself if you don't believe me. I used this function in several DTS packages, and it took some time to find out what caused this huge perfomance issue. Change the split function in this:
CREATE FUNCTION dbo.Split(@String varchar(4000), @Delimiter char(1)) RETURNS @Results TABLE (Items varchar(4000)) AS
BEGIN
if @String is not null begin
--Place the original code here, see --above --(between the BEGIN and the --secondlast END) --Can't finish the function because of --this lousy 1000 characters limit in --this comment :-(
end RETURN END
Other than that, the function is great! (If this comment was disrespectful, please report it.)
3/8/2005 5:14:07 AM:
Its Realy very good example thank you .. it helped me alot (If this comment was disrespectful, please report it.)
But this doesn't work unless a hardcoded string or a value in a variable is sent, can't make joins with it... (If this comment was disrespectful, please report it.)
Problem i'm having is if you want to split a large field, this doesn't work. For example if you have a 1000 word article with page separaters [newpage] in your data, but you want to return multiple rows, this doesn't seem to work. any ideas? (If this comment was disrespectful, please report it.)
I found a fairly big bug in this code when used on strings with trailing spaces. This is because the LEN function does not include trailing spaces, so if there is 1 trailing space you end up with 1 character chopped off every "slice". To fix this I added
set @string = left(@string,len(@string))
before the if. There are probably a few other ways to trim trailing spaces but this is what I used. (If this comment was disrespectful, please report it.)
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular code, please
click here instead.)
By 14 Users
4/4/2003 8:49:40 AM: 
