Split Function

Sql Server does not have in-build Split function.
To achive the same I have created below function.

/****** Object:  UserDefinedFunction [dbo].[fn_Split] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_Split]
(
     @InputStr VARCHAR(MAX) -- List of delimited items
    ,@SplitChar CHAR -- delimiter that separates items 
)
RETURNS @Splittings TABLE
(
     Position INT
    ,Val VARCHAR(20)
)
AS
BEGIN

    DECLARE @Index INT, @LastIndex INT, @SNo INT
    
    SET @LastIndex = 0
    SET @Index = CHARINDEX(@SplitChar, @InputStr)
    SET @SNo = 0
    
    WHILE @Index > 0
    BEGIN  
  SET @SNo = @SNo + 1
        INSERT INTO @Splittings(Position, Val)
        VALUES(@SNo, LTRIM(RTRIM(SUBSTRING(@InputStr, @LastIndex, @Index - @LastIndex)))) 
 
        SET @LastIndex = @Index +1
        SET @Index = CHARINDEX(@SplitChar, @InputStr, @LastIndex)
    END
    SET @SNo = @SNo + 1
    INSERT INTO @Splittings(Position, Val)
    VALUES(@SNo, LTRIM(RTRIM(SUBSTRING(@InputStr, @LastIndex, LEN(@InputStr) - @LastIndex + 1))))
    
    RETURN
END

To Call the Function you can use below query

SELECT * FROM dbo.fn_Split('Chennai,Bangalore,Mumbai',',')  

Posted in: SQL