Hey guys!
Goodnight!

Today I will present a function created by my friend Murilo Mielke, which allows you to break a string delimited by some (or some) characters into sub-strings. For those who are Web developers, this is what the Explode function in PHP or the Split function in Java, Javascript, C#, etc. do.

Basically, you have a string like the example below:

nome;nascimento;email
Nome 1;1994-05-29;[email protected]
Nome 2;1981-07-10;[email protected]
Nome 3;2001-02-27;[email protected]

Imagine that you only want to retrieve the name and email from the records above. Dividing each line using the “;” character as a separator, we have 3 sub-strings. This is exactly what the function below does:

CREATE FUNCTION [dbo].[fncSplit] ( @String varchar(8000), @Separador varchar(8000), @PosBusca int )
RETURNS varchar(8000)
AS BEGIN
    
    DECLARE @Index int, @Max int, @Retorno varchar(8000)

    DECLARE @Partes as TABLE ( Id_Parte int identity(1,1), Texto varchar(8000) )

    SET @Index = charIndex(@Separador,@String)

    WHILE (@Index > 0) BEGIN	
        INSERT INTO @Partes SELECT SubString(@String,1,@Index-1)
        SET @String = Rtrim(Ltrim(SubString(@String,@Index+Len(@Separador),Len(@String))))
        SET @Index = charIndex(@Separador,@String)
    END

    IF (@String != '') INSERT INTO @Partes SELECT @String

    SELECT @Max = Count(*) FROM @Partes

    IF (@PosBusca = 0) SET @Retorno = Cast(@Max as varchar(5))
    IF (@PosBusca < 0) SET @PosBusca = @Max + 1 + @PosBusca
    IF (@PosBusca > 0) SELECT @Retorno = Texto FROM @Partes WHERE Id_Parte = @PosBusca

    RETURN RTRIM(LTRIM(@Retorno))

END
GO

Usage examples:

DECLARE @strOrigem VARCHAR(MAX) = 'Testando|String|Para|O|Blog'

SELECT dbo.fncSplit(@strOrigem, '|', 1) -- Vai imprimir na tela 'Testando'
SELECT dbo.fncSplit(@strOrigem, '|', 5) -- Vai imprimir na tela 'Blog'

Using the CLR

Another alternative to solving this problem is to use the CLR, a feature that allows you to create codes written in .NET (C# or VB.NET) within SQL Server, which generally have much better performance than T-SQL codes. If you don't know what SQLCLR is, find out more by accessing the post Introduction to SQL CLR (Common Language Runtime) in SQL Server.

C# function source code
View C# (CLR) function source code

using System;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncSplit(SqlString Ds_Palavra, SqlString Ds_Delimitador, SqlInt32 Qt_Indice)
    {

        if (Ds_Palavra.IsNull || Qt_Indice.IsNull)
            return SqlString.Null;

        if (Ds_Delimitador.IsNull)
            return Ds_Palavra.Value;

        var indice = (Qt_Indice.Value == 0) ? 1 : Qt_Indice.Value;
        var palavra = Ds_Palavra.Value.Split(new string[] { Ds_Delimitador.Value }, StringSplitOptions.None);

        if (Qt_Indice.Value == 0)
            return palavra.Length.ToString();

        if (palavra.Length < Qt_Indice.Value)
            return SqlString.Null;

        return indice > 0 ? palavra[indice - 1] : palavra[palavra.Length - Math.Abs(indice)];

    }
}

Source code of C# function in T-SQL:
Click here to view the T-SQL code to create this function

-----------------------------------------------------------------------
-- HABILITA O CLR NA INSTÂNCIA
-----------------------------------------------------------------------

IF ((SELECT TOP(1) CONVERT(INT, [value]) FROM sys.configurations WHERE [name] = 'clr enabled') = 0)
BEGIN

    EXEC sys.sp_configure 'advanced options', 1;
    
    RECONFIGURE;
    
    EXEC sys.sp_configure 'clr enabled', 1;
    
    RECONFIGURE;
    
END


-----------------------------------------------------------------------
-- APAGA OS RECURSOS (CASO JÁ EXISTAM)
-----------------------------------------------------------------------

IF (OBJECT_ID('dbo.fncSplit') IS NOT NULL) DROP FUNCTION [dbo].[fncSplit]
GO

IF EXISTS(SELECT TOP(1) NULL FROM sys.assemblies WHERE [name] = 'SQLCLR_Split')
BEGIN
    DROP ASSEMBLY [SQLCLR_Split]
END
GO


-----------------------------------------------------------------------
-- ASSINA O ASSEMBLY (SQL 2017+)
-----------------------------------------------------------------------

DECLARE @Nivel_Compatibilidade TINYINT = (SELECT [compatibility_level] FROM sys.databases WHERE [database_id] = 1)

IF (@Nivel_Compatibilidade >= 140) -- SQL 2017+
BEGIN
    
    EXEC('DECLARE @asmBin varbinary(max) = 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300138875620000000000000000E00022200B01300000080000000600000000000022270000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000D02600004F00000000400000B802000000000000000000000000000000000000006000000C000000982500001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000028070000002000000008000000020000000000000000000000000000200000602E72737263000000B80200000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000042700000000000048000000020005003821000060040000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300500D300000001000011000F00280500000A2D090F02280600000A2B01170C082C0B7E0700000A0D38AE0000000F01280500000A130411042C120F00280800000A280900000A0D388F0000000F02280A00000A2C090F02280A00000A2B01170A0F00280800000A178D0900000125160F01280800000AA2166F0B00000A0B0F02280A00000A16FE01130511052C14078E6913061206280C00000A280900000A0D2B39078E690F02280A00000AFE04130711072C087E0700000A0D2B1F0616300E07078E6906280D00000A599A2B05070617599A280900000A0D2B00092A2202280E00000A002A42534A4201000100000000000C00000076322E302E35303732370000000005006C0000008C010000237E0000F8010000C801000023537472696E677300000000C00300000400000023555300C4030000100000002347554944000000D40300008C00000023426C6F620000000000000002000001471502000900000000FA013300160000010000000C0000000200000002000000030000000E000000040000000100000001000000020000000000C60001000000000006006600300106008600300106003D001D010F005001000006009C01E2000A005100E9000A00AE005F010A0001005F010600BA00E20006008901E20006000400E2000600C100E200000000000A00000000000100010001001000740100001500010001005020000000009600BC014A0001002F21000000008618130106000400000001001300000002000401000003003300090013010100110013010600190013010A003100130106003900D7001D004100D7001D003900DD0021003900A40025003900A30129004100A4002F004900BF0133005900B8002500610019013C002900130106002000230086002E000B0055002E0013005E002E001B007D001000048000000000000000000000000000000000AF01000002000000000000000000000041002A000000000002000000000000000000000041001E0000000000000000000053716C496E743332003C4D6F64756C653E0044735F50616C617672610053797374656D2E44617461006D73636F726C69620051745F496E646963650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650053716C537472696E6700546F537472696E67004D6174680053514C434C525F53706C69742E646C6C006765745F49734E756C6C0053797374656D004D6963726F736F66742E53716C5365727665722E5365727665720044735F44656C696D697461646F72002E63746F72004162730053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730055736572446566696E656446756E6374696F6E7300537472696E6753706C69744F7074696F6E73004F626A656374006F705F496D706C696369740053514C434C525F53706C697400666E6353706C6974000000000000000091EB2A41302F6D4D837C9423505BEC85000420010108032000010520010111110C0708081D0E02111D02020802032000020306111D0320000E050001111D0E032000080820021D0E1D0E1129040001080808B77A5C561934E0890A0003111D111D111D11210801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301080100070100000000040100000000000000001388756200000000020000001C010000B4250000B4070000525344535B7556F0DBE3AD408108A5210459E35F01000000433A5C55736572735C41646D696E6973747261746F725C736F757263655C7265706F735C53514C434C525F53706C69745C53514C434C525F53706C69745C6F626A5C44656275675C53514C434C525F53706C69742E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000F826000000000000000000001227000000200000000000000000000000000000000000000000000004270000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000005C02000000000000000000005C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004BC010000010053007400720069006E006700460069006C00650049006E0066006F0000009801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000042001100010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052005F00530070006C00690074002E0064006C006C00000000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000004A00110001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052005F00530070006C00690074002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000243700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000;

    DECLARE @clrName nvarchar(4000) = ''SQLCLR_Split''
    DECLARE @hash varbinary(64);

    SELECT @hash = HASHBYTES(''SHA2_512'', @asmBin);

    IF (EXISTS(SELECT TOP(1) NULL FROM sys.trusted_assemblies WHERE [hash] = @hash))
    BEGIN

        EXEC sys.sp_drop_trusted_assembly
            @hash = @hash
        
    END;
    
    EXEC sys.sp_add_trusted_assembly 
        @hash = @hash,
        @description = @clrName;')

END
GO


-----------------------------------------------------------------------
-- CRIA O ASSEMBLY
-----------------------------------------------------------------------

CREATE ASSEMBLY [SQLCLR_Split]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300138875620000000000000000E00022200B01300000080000000600000000000022270000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000D02600004F00000000400000B802000000000000000000000000000000000000006000000C000000982500001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000028070000002000000008000000020000000000000000000000000000200000602E72737263000000B80200000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000042700000000000048000000020005003821000060040000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300500D300000001000011000F00280500000A2D090F02280600000A2B01170C082C0B7E0700000A0D38AE0000000F01280500000A130411042C120F00280800000A280900000A0D388F0000000F02280A00000A2C090F02280A00000A2B01170A0F00280800000A178D0900000125160F01280800000AA2166F0B00000A0B0F02280A00000A16FE01130511052C14078E6913061206280C00000A280900000A0D2B39078E690F02280A00000AFE04130711072C087E0700000A0D2B1F0616300E07078E6906280D00000A599A2B05070617599A280900000A0D2B00092A2202280E00000A002A42534A4201000100000000000C00000076322E302E35303732370000000005006C0000008C010000237E0000F8010000C801000023537472696E677300000000C00300000400000023555300C4030000100000002347554944000000D40300008C00000023426C6F620000000000000002000001471502000900000000FA013300160000010000000C0000000200000002000000030000000E000000040000000100000001000000020000000000C60001000000000006006600300106008600300106003D001D010F005001000006009C01E2000A005100E9000A00AE005F010A0001005F010600BA00E20006008901E20006000400E2000600C100E200000000000A00000000000100010001001000740100001500010001005020000000009600BC014A0001002F21000000008618130106000400000001001300000002000401000003003300090013010100110013010600190013010A003100130106003900D7001D004100D7001D003900DD0021003900A40025003900A30129004100A4002F004900BF0133005900B8002500610019013C002900130106002000230086002E000B0055002E0013005E002E001B007D001000048000000000000000000000000000000000AF01000002000000000000000000000041002A000000000002000000000000000000000041001E0000000000000000000053716C496E743332003C4D6F64756C653E0044735F50616C617672610053797374656D2E44617461006D73636F726C69620051745F496E646963650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650053716C537472696E6700546F537472696E67004D6174680053514C434C525F53706C69742E646C6C006765745F49734E756C6C0053797374656D004D6963726F736F66742E53716C5365727665722E5365727665720044735F44656C696D697461646F72002E63746F72004162730053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730055736572446566696E656446756E6374696F6E7300537472696E6753706C69744F7074696F6E73004F626A656374006F705F496D706C696369740053514C434C525F53706C697400666E6353706C6974000000000000000091EB2A41302F6D4D837C9423505BEC85000420010108032000010520010111110C0708081D0E02111D02020802032000020306111D0320000E050001111D0E032000080820021D0E1D0E1129040001080808B77A5C561934E0890A0003111D111D111D11210801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301080100070100000000040100000000000000001388756200000000020000001C010000B4250000B4070000525344535B7556F0DBE3AD408108A5210459E35F01000000433A5C55736572735C41646D696E6973747261746F725C736F757263655C7265706F735C53514C434C525F53706C69745C53514C434C525F53706C69745C6F626A5C44656275675C53514C434C525F53706C69742E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000F826000000000000000000001227000000200000000000000000000000000000000000000000000004270000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000005C02000000000000000000005C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004BC010000010053007400720069006E006700460069006C00650049006E0066006F0000009801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000042001100010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052005F00530070006C00690074002E0064006C006C00000000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000004A00110001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052005F00530070006C00690074002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000243700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    WITH PERMISSION_SET = SAFE;
GO



-----------------------------------------------------------------------
-- CRIA A FUNÇÃO
-----------------------------------------------------------------------

CREATE FUNCTION [dbo].[fncSplit] (
    @Ds_Palavra NVARCHAR (MAX), 
    @Ds_Delimitador NVARCHAR (MAX), 
    @Qt_Indice INT
)
RETURNS NVARCHAR (MAX)
AS
EXTERNAL NAME [SQLCLR_Split].[UserDefinedFunctions].[fncSplit]
GO


/*

Teste:

SELECT dbo.fncSplit('Dirceu;Resende;Lindão', ';', 1)
SELECT dbo.fncSplit('Dirceu;Resende;Lindão', ';', 2)
SELECT dbo.fncSplit('Dirceu;Resende;Lindão', ';', -1)

*/

Performance test

While the two functions bring us the same result, the way they are executed is totally different. The T-SQL function is simpler to implement (just an F5), and the CLR function takes more work to create in the database, as you will need to create an assembly in Visual Studio to use it (if you don't have one. If you already have one created, then it's easy) in addition to the fact that it is written in C#, a language widely known by developers, but not so much by DBA's (All of this has already been discussed in the SQL CLR introduction post).

Given these facts, you may be asking yourself: "So why use the CLR function? What's the advantage?" And the answer is this: PERFORMANCE.

Query used for testing:

SELECT 
    CLR.dbo.fncSplit(Descricao, '|', 1),
    CLR.dbo.fncSplit(Descricao, '|', 2),
    CLR.dbo.fncSplit(Descricao, '|', 3),
    CLR.dbo.fncSplit(Descricao, '|', 4)
FROM 
    dbo.Teste_Group_Concat


SELECT 
    dbo.fncSplit(Descricao, '|', 1),
    dbo.fncSplit(Descricao, '|', 2),
    dbo.fncSplit(Descricao, '|', 3),
    dbo.fncSplit(Descricao, '|', 4)
FROM 
    dbo.Teste_Group_Concat

Result:

SQL Server - Split function CLR TSQL performance
SQL Server - Split function CLR TSQL performance

As you can see, the test table has 81,753 lines, where each line has 4 words separated by the character “|”, and I applied the function 4 times, to retrieve each of these words, from each line.

The result presented a very convincing argument for using the CLR function: While the T-SQL function took 213.2 seconds to perform this processing, the CLR function (which does the same thing) needed only 3.6 seconds. A difference in performance of almost 60 times more, if using the CLR.
Incredible!

sql server split explodes divide string strings table valued function

sql server split explodes divide string strings table valued function

Just like that!
Until the next post.