Skip to main content

A Transact SQL function to SPLIT string via delimiter into several strings (columns) and return given column.

--
-- T-SQL SPLIT function
--

-- FN_STRING_HELPER_SPLIT(try_to_split_this, '_', 1)
--> try
-- FN_STRING_HELPER_SPLIT(try_to_split_this, '_', 3)
--> split
-- FN_STRING_HELPER_SPLIT(try_to_split_this, '_', 100)
--> this

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FN_STRING_HELPER_SPLIT]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].FN_STRING_HELPER_SPLIT
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ============================================================================
-- Split String
--        Name: FN_STRING_HELPER_SPLIT
-- Description: A Function to SPLIT string via delimiter into several strings
--              (columns) and return given column.
--        Date: 2015-03-08 13:02:58
-- ============================================================================

CREATE FUNCTION [dbo].FN_STRING_HELPER_SPLIT (
    @strToSplit as varchar(4000),
    @delimiter as varchar(50),
    @columnToReturn as int
)
RETURNS varchar(25)
AS
BEGIN
    -- Declaration of the return variable
    DECLARE @i as int
    DECLARE @occurrences as int
    DECLARE @delimiterIndex as int
    DECLARE @actSplitValue as varchar(4000)

    -- T-SQL statements to compute the return value here
    SET @i = 1
    SET @delimiterIndex = 1

    WHILE @i <= @columnToReturn
    BEGIN
        SET @delimiterIndex = CHARINDEX(@delimiter, @strToSplit)
        -- if not found end while
        SET @actSplitValue = @strToSplit
        IF @delimiterIndex = 0 BEGIN
            BREAK
        END
        SET @actSplitValue = LEFT(@strToSplit, @delimiterIndex - 1)
        -- SUBSTRING(string, von, bis)
        SET @strToSplit = SUBSTRING(@strToSplit ,@delimiterIndex +1, LEN(@strToSplit)- @delimiterIndex)
        SET @i = @i + 1
    END
  RETURN @actSplitValue
END

GO