SQL Server - Breaking Substring Strings Using Split String

Views: 18.411 views

In this article

This post is the 1 part of 5 in the series. string Split

SQL Server - Breaking strings into sub-strings using a separator (Split string)
SQL Server - Table-valued function to break a string into lines up to N characters long
How to wrap a string in a substring table using a delimiter in SQL Server
SQL Server - Charindex: A Different Function to Break Split Strings
SQL Server - Using STRING_SPLIT to transform strings from a row into columns

Reading Time: 4 minutes

Hello guys!
Good night!

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 in substrings. For the web developer, that's what makes the explode function of PHP or Java Split, Javascript, C #, etc ..

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 want to retrieve only 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

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

Examples of use:

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'

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 solve this problem is to use CLR, a feature that allows you to create .NET (C # or VB.NET) code written within SQL Server, where it generally performs much better than T-SQL code. If you don't know what SQLCLR is, learn more by accessing the post. Introduction to SQL Common Language Runtime (CLR) in SQL Server.

C# function source code
View C # Function (CLR) 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)];

    }
}

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)];

}

C# function source code 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)

*/

100

-----------------------------------------------------------------------

-- 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]

IF EXISTS(SELECT TOP(1) NULL FROM sys.assemblies WHERE [name] = 'SQLCLR_Split')

BEGIN

DROP ASSEMBLY [SQLCLR_Split]

END

-----------------------------------------------------------------------

-- 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

-----------------------------------------------------------------------

-- CRIA O ASSEMBLY

-----------------------------------------------------------------------

CREATE ASSEMBLY [SQLCLR_Split]

AUTHORIZATION [dbo]

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300138875620000000000000000E00022200B01300000080000000600000000000022270000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000D02600004F00000000400000B802000000000000000000000000000000000000006000000C000000982500001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000028070000002000000008000000020000000000000000000000000000200000602E72737263000000B80200000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000042700000000000048000000020005003821000060040000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300500D300000001000011000F00280500000A2D090F02280600000A2B01170C082C0B7E0700000A0D38AE0000000F01280500000A130411042C120F00280800000A280900000A0D388F0000000F02280A00000A2C090F02280A00000A2B01170A0F00280800000A178D0900000125160F01280800000AA2166F0B00000A0B0F02280A00000A16FE01130511052C14078E6913061206280C00000A280900000A0D2B39078E690F02280A00000AFE04130711072C087E0700000A0D2B1F0616300E07078E6906280D00000A599A2B05070617599A280900000A0D2B00092A2202280E00000A002A42534A4201000100000000000C00000076322E302E35303732370000000005006C0000008C010000237E0000F8010000C801000023537472696E677300000000C00300000400000023555300C4030000100000002347554944000000D40300008C00000023426C6F620000000000000002000001471502000900000000FA013300160000010000000C0000000200000002000000030000000E000000040000000100000001000000020000000000C60001000000000006006600300106008600300106003D001D010F005001000006009C01E2000A005100E9000A00AE005F010A0001005F010600BA00E20006008901E20006000400E2000600C100E200000000000A00000000000100010001001000740100001500010001005020000000009600BC014A0001002F21000000008618130106000400000001001300000002000401000003003300090013010100110013010600190013010A003100130106003900D7001D004100D7001D003900DD0021003900A40025003900A30129004100A4002F004900BF0133005900B8002500610019013C002900130106002000230086002E000B0055002E0013005E002E001B007D001000048000000000000000000000000000000000AF01000002000000000000000000000041002A000000000002000000000000000000000041001E0000000000000000000053716C496E743332003C4D6F64756C653E0044735F50616C617672610053797374656D2E44617461006D73636F726C69620051745F496E646963650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650053716C537472696E6700546F537472696E67004D6174680053514C434C525F53706C69742E646C6C006765745F49734E756C6C0053797374656D004D6963726F736F66742E53716C5365727665722E5365727665720044735F44656C696D697461646F72002E63746F72004162730053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730055736572446566696E656446756E6374696F6E7300537472696E6753706C69744F7074696F6E73004F626A656374006F705F496D706C696369740053514C434C525F53706C697400666E6353706C6974000000000000000091EB2A41302F6D4D837C9423505BEC85000420010108032000010520010111110C0708081D0E02111D02020802032000020306111D0320000E050001111D0E032000080820021D0E1D0E1129040001080808B77A5C561934E0890A0003111D111D111D11210801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301080100070100000000040100000000000000001388756200000000020000001C010000B4250000B4070000525344535B7556F0DBE3AD408108A5210459E35F01000000433A5C55736572735C41646D696E6973747261746F725C736F757263655C7265706F735C53514C434C525F53706C69745C53514C434C525F53706C69745C6F626A5C44656275675C53514C434C525F53706C69742E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000F826000000000000000000001227000000200000000000000000000000000000000000000000000004270000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000005C02000000000000000000005C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004BC010000010053007400720069006E006700460069006C00650049006E0066006F0000009801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000042001100010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052005F00530070006C00690074002E0064006C006C00000000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000004A00110001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052005F00530070006C00690074002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000243700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH PERMISSION_SET = SAFE;

-----------------------------------------------------------------------

-- CRIA A FUNÇÃO

-----------------------------------------------------------------------

CREATE FUNCTION [dbo].[fncSplit] (

@Ds_Palavra NVARCHAR (MAX),

@Ds_Delimitador NVARCHAR (MAX),

@Qt_Indice INT

)

RETURNS NVARCHAR (MAX)

EXTERNAL NAME [SQLCLR_Split].[UserDefinedFunctions].[fncSplit]

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 be created in the database, as it will be necessary to create an assembly in Visual Studio to use it (if you don't have any). 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 was already mentioned in the SQL CLR introduction post).

Given these facts, you may be asking yourself, “So why use the CLR function? What is 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

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:

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 those 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 do this processing, the CLR function (which does the same thing) needed only 3,6 seconds. A performance difference of almost 60 times more if using the CLR.
Amazing!

sql server split explode split string strings table valued function

That simple!
Until the next post.

Roberto Apolinário da Silva said:

11 June 2021 to 03: 29

Perfect this article and the Split function. I made some adjustments for the function to handle different types of data and also when null. Abs.

nego said:

27 February 2018 to 16: 45

The function is very good… Congratulations!

Osmar Fernandes said:

24 June 2016 to 15: 32

I created the function but it is only bringing NULL as a result.

Would you help me?

- Dirceu Resende said:
  
  24 June 2016 to 15: 47
  
  Osmar, good afternoon and thanks for stopping by!
  
  About your question .. What version of SQL Server are you using? How are you performing the function?
  
Odilon AM said:

21 from 2015 from September to 14: 46

It broke a huge branch, Thanks!

israelsep said:

13 February 2015 to 16: 46

Very useful, thanks!

Under development

December 17, 2020

[…] FncSplit function […]

Other Languages

Subscribe to a blog by email

Blog Views

Categories

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Recent Posts

Other Languages

Are you looking for something?

Subscribe to Blog by Email

Total Views

Courses and training

Microsoft MVP

Acknowledgments and Certificates

Categories

Archives

Recent Posts

SQL Server - Breaking strings into sub-strings using a separator (Split string)

Using the CLR

Performance test

sql server split explode split string strings table valued function

You may also like ...

Comments

Leave a Comment Cancel reply

Using the CLR

Performance test

sql server split explode split string strings table valued function

You may also like ...

SQL Server - How to know the date of a user's last login

SQL Server and Azure SQL Database - How to return CPU and Memory usage using T-SQL

SQL Server - How to use auditing to map actual required permissions on a user

Comments

Leave a Comment Cancel reply