IF EXISTS (SELECT 'x' FROM sysobjects WHERE name = 'Split')
DROP FUNCTION Split
GO
CREATE FUNCTION Split
(@String as varchar(4000),
@Delimiter as varchar(4000),
@Index as int)
RETURNS varchar(4000)
AS
BEGIN
/*
Author: Philip Leitch
Date: 2001
Purpose: Creates an ability to split a varchar (string) into an array and access specific elements of that array.
Copyright: Philip Leitch 2001
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
To locate a specific section out of a string we have to get a start and a length.
The start will be gained by itterate through the string @Index number of times.
The length will be gained by determining the number of characters to the next @Delimiter,
or to the end of the string, whichever is first.
*/
Declare @FoundIndex as int
declare @Length as int
declare @Itteration as int
declare @RemainingString as varchar(4000)
Declare @ReturnString as varchar(4000)
Set @Itteration = 0
set @FoundIndex = 0
set @RemainingString = @string
while @Itteration <= @Index+1
begin
set @FoundIndex = CHARINDEX (@Delimiter, @RemainingString )
if @FoundIndex = 0
begin
--This means that there are no more @Delimiter strings left - so if the
--@Index is met we return what we have, otherwise we return nothing (@Index does not exist)
if @Index = @Itteration
set @ReturnString = @RemainingString
else
set @ReturnString = ''
break
end
else
begin
--This means that we found a @Dilimiter within the remaining string
--If we are not at the correct index then we shorten the remaining string.
if @Itteration = @Index
begin
--We are at the correct @Index - so now we need to find a length.
set @FoundIndex = CHARINDEX (@Delimiter, @RemainingString )
if @FoundIndex > 0
begin
--There is at least another delimiter so we need to remove everything after that one
set @ReturnString = left(@RemainingString, @FoundIndex -1)
end
end
else
set @RemainingString = SUBSTRING ( @RemainingString , @FoundIndex+len(@delimiter + '!')-1 , len(@RemainingString) - @FoundIndex )
end
set @Itteration = @Itteration +1
end
RETURN @ReturnString
End
GO
-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.Split('Hello, this is a test, to see if the split works, which I am sure it does, and if it does you will see a message saying, This function is working normally, okay?', ', ',5)