Split a delimited string of ID into table rows

Use CHARINDEX

CREATE FUNCTION [dbo].[fn_SplitString] (
	@IDs VARCHAR(MAX)
	,@Delimiter CHAR(1)
	)
RETURNS @SplittedIds TABLE (ID VARCHAR(255))
AS
BEGIN
	DECLARE @SplitLength INT
	DECLARE @ID VARCHAR(255)

	WHILE LEN(@IDs) > 0
	BEGIN
		SELECT @SplitLength = (
				CASE CHARINDEX(@Delimiter, @IDs)
					WHEN 0
						THEN LEN(@IDs)
					ELSE CHARINDEX(@Delimiter, @IDs) - 1
					END
				)

		SET @ID = SUBSTRING(@IDs, 1, @SplitLength)

		INSERT INTO @SplittedIds
		VALUES (@ID)

		SELECT @IDs = (
				CASE (LEN(@IDs) - @SplitLength)
					WHEN 0
						THEN ''
					ELSE RIGHT(@IDs, LEN(@IDs) - @SplitLength - 1)
					END
				)
	END

	RETURN
END

Use PATINDEX

CREATE FUNCTION [dbo].[fn_SplitStringByPatindex] (
	@IDs VARCHAR(MAX)
	,@SPLITTER VARCHAR(255)
	)
RETURNS @SplittedIds TABLE (ID VARCHAR(255))
AS
BEGIN
	DECLARE @ID VARCHAR(255)
	DECLARE @nINDEX INT
	DECLARE @P_IDs VARCHAR(MAX)

	SET @nINDEX = 1
	SET @IDs = @IDs + @SPLITTER
	SET @SPLITTER = '%' + @SPLITTER + '%'

	WHILE @nINDEX > 0
	BEGIN
		SET @nINDEX = PATINDEX(@SPLITTER, @IDs)

		IF @nINDEX > 0
		BEGIN
			SET @P_IDs = SUBSTRING(@IDs, 1, @nINDEX)
			SET @IDs = REPLACE(@IDs, @P_IDs, '')

			INSERT INTO @SplittedIds
			VALUES (SUBSTRING(@P_IDs, 1, LEN(@P_IDs) - 1))
		END
	END

	RETURN
END

Split SQL String