DECLARE @Str varchar(100)
SET @Str = 'A,B,C,D'
SELECT * FROM STRING_SPLIT(@Str,',')
CREATE FUNCTION [dbo].[fnStringSplit]
(
@Str VARCHAR(MAX)
, @Separator CHAR(1)
)
RETURNS @RESULT TABLE(Value VARCHAR(MAX))
AS
BEGIN
DECLARE @SeparatorPosition int = CHARINDEX(@Separator, @Str )
DECLARE @Value VARCHAR(MAX), @StartPosition int = 1
IF @SeparatorPosition = 0
BEGIN
INSERT INTO @RESULT VALUES (@Str)
RETURN
END
SET @Str = @Str + @Separator
WHILE @SeparatorPosition > 0
BEGIN
SET @Value = SUBSTRING(@Str , @StartPosition, @SeparatorPosition- @StartPosition)
IF( @Value <> '' )
INSERT INTO @RESULT VALUES(@Value)
SET @StartPosition = @SeparatorPosition + 1
SET @SeparatorPosition = CHARINDEX(@Separator, @Str , @StartPosition)
END
RETURN
END
GO