SQL SERVER - How to split a comma separated string / delimited text

Use this SQL Server Function to split any string delimited text and get back items as a recordset.


CREATE FUNCTION dbo.fnSplit(
    @string2split VARCHAR(8000) -- String to Split
  , @delimiter VARCHAR(8000) = ',' -- delimiter that separates string items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@delimiter,@string2split,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@string2split,1,CHARINDEX(@delimiter,@string2split,0)-1))),
  @string2split=RTRIM(LTRIM(SUBSTRING(@string2split,CHARINDEX(@delimiter,@string2split,0)+LEN(@delimiter),LEN(@string2split))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@string2split) > 0
 INSERT INTO @List SELECT @string2split -- Put the last item in
RETURN
END
GO


Query:
SELECT * FROM dbo.fnSplit('Sudan, Congo DRC, Kenya, Uganda, Bahrain',',')

Result:

Sudan
Congo DRC
Kenya
Uganda
Bahrain