Hoy les presentaré una función creada por mi amigo. Murilo Mielke, que le permite dividir una cadena delimitada por algunos (o algunos) caracteres en subcadenas. Para aquellos que son desarrolladores web, esto es lo que hace la función Explosionar en PHP o la función Dividir en Java, Javascript, C#, etc.
Imagine que sólo desea recuperar el nombre y el correo electrónico de los registros anteriores. Dividiendo cada línea usando el “;” carácter como separador, tenemos 3 subcadenas. Esto es exactamente lo que hace la siguiente función:
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
Ejemplos 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'
Usando el CLR
Otra alternativa para solucionar este problema es utilizar CLR, una característica que permite crear códigos escritos en .NET (C# o VB.NET) dentro de SQL Server, que generalmente tienen un rendimiento mucho mejor que los códigos T-SQL. Si no sabes qué es SQLCLR, infórmate accediendo al post Introducción a SQL CLR (Common Language Runtime) en SQL Server.
Código fuente de la función C# Ver el código fuente de la función 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)];
}
}
Código fuente de la función C# en T-SQL: Haga clic aquí para ver el código T-SQL para crear esta función
-----------------------------------------------------------------------
-- 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)
*/
Prueba de rendimiento
Si bien las dos funciones nos traen el mismo resultado, la forma en que se ejecutan es totalmente diferente. La función T-SQL es más sencilla de implementar (solo un F5), y la función CLR requiere más trabajo para crearla en la base de datos, ya que necesitarás crear un ensamblado en Visual Studio para usarla (si no tienes uno. Si ya tienes uno creado, entonces es fácil) además de que está escrito en C#, un lenguaje ampliamente conocido por los desarrolladores, pero no tanto por los DBA (todo esto ya se discutió en la publicación de introducción a SQL CLR).
Teniendo en cuenta estos hechos, es posible que se pregunte: "¿Entonces por qué utilizar la función CLR? ¿Cuál es la ventaja?" Y la respuesta es esta: RENDIMIENTO.
Resultado: SQL Server: rendimiento de CLR TSQL de función dividida
Como puedes ver, la tabla de prueba tiene 81,753 líneas, donde cada línea tiene 4 palabras separadas por el carácter “|”, y apliqué la función 4 veces, para recuperar cada una de estas palabras, de cada línea.
El resultado presentó un argumento muy convincente para usar la función CLR: mientras que la función T-SQL tardó 213,2 segundos en realizar este procesamiento, la función CLR (que hace lo mismo) solo necesitó 3,6 segundos. Una diferencia de rendimiento de casi 60 veces mayor, si se utiliza el CLR.
¡Increíble!
La división del servidor SQL explota la función de valores de la tabla de cadenas de cadenas divididas
La división del servidor SQL explota la función de valores de la tabla de cadenas de cadenas divididas
¡Así!
Hasta el próximo post.
Dirceu Resende
Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.
Comentários (0)
Carregando comentários…