This is a script to conver a delimited/separated values given a delimiter into a table, via a User Defined Function.

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