Skip to content

Instantly share code, notes, and snippets.

@vendettamit
Created January 3, 2019 17:24
Show Gist options
  • Save vendettamit/a1025e0a14fcd45015a5f8b5c6943d1a to your computer and use it in GitHub Desktop.
Save vendettamit/a1025e0a14fcd45015a5f8b5c6943d1a to your computer and use it in GitHub Desktop.
Split string with Index number
CREATE FUNCTION [dbo].[fnSplitIndexed](
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (idx integer, item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
Declare @idx integer = 0
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
BEGIN
Set @idx += 1
INSERT INTO @List SELECT @idx, @sItem
END
END
IF LEN(@sInputList) > 0
BEGIN
Set @idx += 1
INSERT INTO @List SELECT @idx, @sInputList -- Put the last item in
END
RETURN
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment