Split in MS SQL SERVER

Here I shall describe about split in MS SQL.

There is no built in function for splitting your text, that’s why I am describing two customize function for performing split operation in MS SQL.


Split function 1: This function returns table that’s why we can say it table valued function.

--select * from fnSplit('1,22,333,444,,5555,666', ',')
CREATE FUNCTION dbo.fnSplit(
  @sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
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
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

Split function 2. This function return single nvarchar output. It takes “string”,”split char”,”index” as input parameter.

-- SELECT dbo.fnSplit('4.55.108.2','.', 2)
ALTER FUNCTION [dbo].[fnSplit]

(
@Expression NVARCHAR(max)
, @Delimiter  NVARCHAR(max)
, @INDEX      INT
)

RETURNS NVARCHAR(max)
AS
BEGIN
DECLARE @RETURN  NVARCHAR(max)
DECLARE @Pos     INT
DECLARE @PrevPos INT
DECLARE @I       INT


IF @Expression IS NULL OR @Delimiter IS NULL OR LEN(@Delimiter) = 0 OR @INDEX < 1
SET @RETURN = NULL
ELSE IF @INDEX = 1 BEGIN
SET @Pos = CHARINDEX(@Delimiter, @Expression, 1)
IF @Pos > 0 SET @RETURN = LEFT(@Expression, @Pos - 1)
END ELSE BEGIN
SET @Pos = 0
SET @I = 0
WHILE (@Pos > 0 AND @I < @INDEX) OR @I = 0 BEGIN
SET @PrevPos = @Pos
SET @Pos = CHARINDEX(@Delimiter, @Expression, @Pos + LEN(@Delimiter))
SET @I = @I + 1
END
IF @Pos = 0 AND @I = @INDEX
SET @RETURN = SUBSTRING(@Expression, @PrevPos + LEN(@Delimiter), LEN(@Expression))
ELSE IF @Pos = 0 AND @I <> @INDEX
SET @RETURN = NULL
ELSE
SET @RETURN = SUBSTRING(@Expression, @PrevPos + LEN(@Delimiter), @Pos - @PrevPos - LEN(@Delimiter))
END
RETURN @RETURN
END
  • For the help please use http://www.google.com

  • We just couldnt leave your website before saying that we genuinely enjoyed the high quality information you offer for your visitors… Would be back frequently to check up on new stuff you post!

    • Thanks. Hope you will with us..