This is a script to conver a delimited/separated values given a delimiter into a table, via a User Defined Function.[more]
This has saved me a lot of work already including just now so I gfigured this is a good share
My apologies as I could not determine where I based this script from but definitely I got it from someone else rather than created my own. Had a few revisions along the way.
CREATE FUNCTION [dbo].[Split]
(
@String VARCHAR(200),
@Delimiter VARCHAR(5)
)
RETURNS @SplittedValues TABLE
(
Ordinal SMALLINT IDENTITY(1,1) PRIMARY KEY,
SplitValue VARCHAR(MAX)
)
AS
BEGIN
DECLARE @SplitLength INT
WHILE LEN(@String) > 0
BEGIN
SELECT @SplitLength =
(CASE CHARINDEX(@Delimiter,@String)
WHEN 0 THEN LEN(@String)
ELSE CHARINDEX(@Delimiter,@String) -1
END)
INSERT INTO @SplittedValues
SELECT SUBSTRING(@String,1,@SplitLength)
SELECT @String =
(CASE (LEN(@String) – @SplitLength)
WHEN 0 THEN ''
ELSE RIGHT(@String, LEN(@String) – @SplitLength – 1)
END)
END
RETURN
END
GO