SQL Server - Quebrando strings em sub-strings utilizando separador (Split string)

Visualizações: 19.084 views

Neste artigo

Esse post é a parte 1 de 5 da série String Split

SQL Server – Quebrando strings em sub-strings utilizando separador (Split string)
SQL Server – Função table-valued para quebrar uma string em linhas com tamanho de até N caracteres
Como quebrar um string em uma tabela de substrings utilizando um delimitador no SQL Server
SQL Server – charindexada: Uma função diferente para quebrar strings delimitadas (split)
SQL Server – Utilizando a STRING_SPLIT para transformar strings de uma linha em colunas

Tempo de Leitura: 4 minutos

Olá Pessoal!
Boa noite!

Hoje eu vou apresentar uma função criada pelo meu amigo Murilo Mielke, que permite quebrar uma string delimitada por algum (ou alguns) caracter em sub-strings. Para quem é desenvolvedor Web, é o que faz a função explode do PHP ou a Split do Java, Javascript, C#, etc..

Basicamente, você tem uma string como o exemplo abaixo:
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 que você queira recuperar apenas o nome e o e-mail dos registros acima. Dividindo cada linha utilizando o caracter “;” como separador, temos uma 3 sub-strings. É exatamente isso que a função abaixo faz:

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

Exemplos de uso:

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'

Utilizando o CLR

Uma outra alternativa para resolver esse problema é utilizar o CLR, recurso que permite criar códigos escritos em .NET (C# ou VB.NET) dentro do SQL Server, onde geralmente possuem um desempenho bem superior aos códigos T-SQL. Caso você não saiba o que é o SQLCLR, saiba mais acessando o post Introdução ao SQL CLR (Common Language Runtime) no SQL Server.

Código-fonte da função C#
Visualizar código-fonte da função C# (CLR)

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ódigo-fonte da função C# em T-SQL:
Clique aqui para visualizar o código T-SQL para criar essa função

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

Teste de performance

Enquanto as duas funções nos trazem o mesmo resultado, a forma de execução delas é totalmente diferente. A função T-SQL é mais simples de ser implementada (basta um F5), e a função CLR dá mais trabalho para ser criada no banco, pois será necessário criar um assembly no Visual Studio para utilizá-la (caso você não tenha nenhum. Se você já tem um criado, aí é fácil) além do fato de ser escrita em C#, uma linguagem amplamente conhecida por desenvolvedores, mas nem tanto por DBA’s (Tudo isso já foi falado no post de introdução do SQL CLR).

Dado essas fatos, você deve estar se perguntando: “Então porque utilizar a função CLR? Qual a vantagem?”. E a resposta é essa: PERFORMANCE.

Query utilizada para os testes:

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

Resultado:

Como vocês podem observar, a tabela de testes possui 81.753 linhas, onde cada linha possui 4 palavras separadas pelo caracter “|”, e apliquei a função 4 vezes, para recuperar cada uma dessas palavras, de cada linha.

O resultado apresentou um argumento bem convincente para utilizar a função CLR: Enquanto a função T-SQL demorou 213,2 segundos para realizar esse processamento, a função CLR (que faz a mesma coisa) precisou de apenas 3,6 segundos. Uma diferença de performance de quase 60 vezes a mais, se utilizando o CLR.
Incrível!

sql server split explode divide string strings table valued function

Simples assim!
Até o próximo post.

Roberto Apolinário da Silva disse:

11 de junho de 2021 às 03:29

Perfeito esse artigo e a função Split. Fiz uns ajustes para a função tratar diferentes tipos de dados e também quando a null. Abs.

Responder
Nego disse:

27 de fevereiro de 2018 às 16:45

Muito boa a função… Parabéns !

Responder
Osmar Fernandes disse:

24 de junho de 2016 às 15:32

Criei a função mas só esta trazendo NULL como resultado.

Poderia me ajudar?

Responder
- Dirceu Resende disse:
  
  24 de junho de 2016 às 15:47
  
  Osmar, boa tarde e obrigado pela visita!
  
  Sobre a sua dúvida.. Qual a versão do SQL Server que você está utilizando? Como você está executando a função?
  
  Responder
Odilon AM disse:

21 de setembro de 2015 às 14:46

Me quebrou um galho imenso, Valeu!

Responder
israelsep disse:

13 de fevereiro de 2015 às 16:46

Muito útil, obrigado!

Responder

Em desenvolvimento

17 de dezembro de 2020

[…] Função fncSplit […]

Assinar blog por e-mail

Visualizações do Blog

Power Alerts

Curso Adm de Bancos no Azure

Curso Segurança de SQL Server

Minhas Certificações

Arquivo de Posts

Categorias

Posts recentes

SQL Server – Quebrando strings em sub-strings utilizando separador (Split string)

Utilizando o CLR

Teste de performance

sql server split explode divide string strings table valued function

You may also like...

7 Responses

Deixe uma respostaCancelar resposta

Utilizando o CLR

Teste de performance

sql server split explode divide string strings table valued function

You may also like...

SQL Server – Como utilizar auditoria para mapear permissões necessárias reais em um usuário

SQL Server – O perigo de utilizar JOIN entre colunas de tipos de dados diferentes

SQL Server – Como exportar o código-fonte de todas as Stored Procedures, Functions, Views e Triggers de um database para arquivos .sql

7 Responses

Deixe uma respostaCancelar resposta