EXAMPLE
CREATE FUNCTION [dbo].[STRINGSPLIT](@SOURCESQL VARCHAR(8000),@STRSEPRATE VARCHAR(10), @POSITION INT)
RETURNS VARCHAR(100) --RETURNS @TEMP TABLE(A VARCHAR(100))
BEGIN
DECLARE @I INT
DECLARE @J INT
DECLARE @TEMP VARCHAR(100)
SET @SOURCESQL=RTRIM(LTRIM(@SOURCESQL))
SET @I=CHARINDEX(@STRSEPRATE,@SOURCESQL)
SET @J = 1;
WHILE @I>=1
BEGIN
IF @j = @POSITION
BEGIN
SET @TEMP = LEFT(@SOURCESQL,@I-1)
--INSERT @TEMP VALUES(LEFT(@SOURCESQL,@I-1))
END
SET @SOURCESQL=SUBSTRING(@SOURCESQL,@I+1,LEN(@SOURCESQL)-@I)
SET @I=CHARINDEX(@STRSEPRATE,@SOURCESQL)
SET @j = @j + 1;
END
IF @SOURCESQL<>'\' AND @j = @POSITION
BEGIN
--INSERT @TEMP VALUES(@SOURCESQL)
SET @TEMP = @SOURCESQL
END
RETURN @TEMP
END
Use the function like below
this function split the given string('willington,robinson') and split by (',') and returns position ones value
SELECT 1 as Col, dbo.STRINGSPLIT('willington,robinson', ',',1) FROM USERS