SQL-Split String Values

-- =============================================
-- Author: S.Ramesh
-- Create date: 07-05-2006
-- Description: To split the string as separate parts
-- =============================================

ALTER FUNCTION [dbo].[udf_SplitString]
(
@vchString VARCHAR(MAX)
, @vchDelimitter CHAR(1)= ','
)
RETURNS @Values TABLE (Parts VARCHAR(MAX))AS
BEGIN
DECLARE @intPosistion INT -- to hold the string posistion
DECLARE @Part VARCHAR(MAX)
SELECT @intPosistion = 1
WHILE (@intPosistion > 0)
BEGIN
-- check the given delimitter in between names else consider it as single string
SELECT @intPosistion = CHARINDEX(@vchDelimitter,@vchString)
IF @intPosistion > 0
BEGIN
-- then split the name
SELECT @Part = LEFT(@vchString,@intPosistion - 1)
END
ELSE
BEGIN
-- if string doesnt have specified delimitter then take it as single string
SELECT @Part = @vchString
-- Set posistion = -1 to end loop
SELECT @intPosistion = -1
END
-- insert the splitted part to return as row value
INSERT @Values(Parts)
SELECT LTRIM(RTRIM(@Part))
SELECT @vchString = RIGHT(@vchString,LEN(@vchString) - @intPosistion)
-- if there are no chanraters in given string then end loop
IF LEN(@vchString) = 0
BEGIN
-- Set posistion = -1 to end loop
SELECT @intPosistion = -1
END
END
RETURN
END