Olá pessoal!
Animados para mais um post ???
Nesse artigo eu gostaria de demonstrar para vocês como podemos utilizar expressões regulares (RegExp) no seu banco de dados através de consultas, sejam elas para criar algum monitoramento ou mesmo criar relatórios e indicadores de BI ou algum sistema.
A expressão regular (ou os estrangeirismos regex ou regexp, abreviação do inglês regular expression) provê uma forma concisa e flexível de identificar cadeias de caracteres de interesse, como caracteres particulares, palavras ou padrões de caracteres. Em outras palavras, a expressão regular é uma forma de permitir realizar, de forma simples, operações com strings extremamente complexas, que demandariam várias condições para tratar esses dados.
Como vocês devem saber, o dialeto do Regexp não é algo que é senso comum entre as linguagens de programação. Existem várias implementações diferentes e as regras da expressão regular são similares, mas podem mudar entre uma linguagem e outra. Ex: Uma expressão regexp que funciona no .NET pode não funcionar no PHP ou no Java e vice-versa.
No SQL Server, podemos utilizar 2 dialetos para utilizar nossas expressões regulares:
- .NET: Dialeto disponível utilizando o SQLCLR (disponível a partir do SQL Server 2005) e a biblioteca system.text.regularexpression do .NET Framework
- ECMA: Dialeto disponível utilizando OLE Automation (disponível a partir do SQL Server 2000) e o módulo VBScript.RegExp. Esse dialeto é o mesmo utilizado pelo Javascript
Para termos de comparação:
- Regexp nativo do SQL Server (utilizando LIKE ou PATINDEX): Limitado (quando comparado aos dialetos mais comuns) e tem a melhor performance entre os 3
- .NET: Tão completo quanto o dialeto ECMA (ou seja, possui MUITO mais recursos que o nativo do SQL Server), tem a performance um pouco abaixo do nativo do SQL Server, mas MUITO (muito mesmo) acima do ECMA (por conta do OLE Automation, não do dialeto em si).
- ECMA: Tão completo quanto o dialeto .NET (ou seja, possui MUITO mais recursos que o nativo do SQL Server) mas tem uma performance muito ruim quando comparado aos outros 2. A sua vantagem sobre o .NET é que não exige a criação de objetos SQLCLR e está disponível no SQL Server 2000.
O intuito desse post é demonstrar como utilizar expressões regulares no SQL Server e mostrar alguns exemplos simples sobre isso. O foco aqui, não é te tornar especialista em expressão regular. Regexp não foi criado para ser facilmente entendido. Requer muito esforço e estudo para dominar essa linguagem. Para isso, sugiro o excelente site Regular-Expressions.info e também o site brasileiro do Aurélio Jargas.
Para ajudar a entender o uso das expressões regulares (dialetos .NET e ECMA), segue imagem abaixo, que extraí do site RegExLib:
Alternativa #1: LIKE e PATINDEX
Utilizando essa solução nativa do SQL Server, você poderá utilizar expressões regulares simples, mas bem úteis no seu dia a dia, e com uma boa performance de execução. A vantagem desse método, é que não requer nenhuma permissão adicional no banco, nem a necessidade de habilitar nenhum recurso avançado ou criar novos objetos no banco.
Visualizar conteúdoCase sensitive
Filtrando a primeira letra
Filtrando as primeiras letras + Case sensitive
Aplicando um filtro personalizado e específico
Utilizando o operador de negação (^)
Utilizando o operador de escape
Identificando caracteres especiais
Precisa remover caracteres especiais? Saiba mais acessando o post Como remover acentuação e caracteres especiais de uma string no SQL Server.
Utilizando números
Validando e-mails
1 2 3 4 5 6 7 8 9 10 | DECLARE @Teste TABLE ( [Email] VARCHAR(200) ) INSERT INTO @Teste VALUES ('[email protected]'), ('Dirceu'), ('Dirceu@'), ('@'), ('@.com'), ('[email protected]'), ('[email protected]'), ('[email protected]'), ('[email protected].') SELECT * FROM @Teste WHERE [Email] NOT LIKE '%[^a-z,0-9,@,.-_]%' AND [Email] LIKE '%_@_%_.__%' AND [Email] NOT LIKE '%_@@_%_.__%' |
Recuperando apenas a parte numérica de uma string
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | CREATE FUNCTION [dbo].[fncRecupera_Numeros] ( @str VARCHAR(MAX) ) RETURNS VARCHAR(500) BEGIN DECLARE @startingIndex INT SET @startingIndex = 0 WHILE (1 = 1) BEGIN -- Vamos identificar todos os caracteres não-numéricos (não estão no intervalo 0-9) e substituir por '' SET @startingIndex = PATINDEX('%[^0-9]%', @str) IF @startingIndex <> 0 SET @str = REPLACE(@str, SUBSTRING(@str, @startingIndex, 1), '') ELSE BREAK END RETURN @str END |
Quer saber mais sobre validação de dados? Acesse os posts abaixo:
– Validando CPF, CNPJ, E-mail, Telefone e CEP no SQL Server
– Como validar a inscrição estadual para todos os estados utilizando o C# (CSharp) e o SQL Server CLR
– Como validar inscrição estadual usando função T-SQL no SQL Server
Alternativa #2: Dialeto .NET com SQLCLR
Com esse método, você terá acesso a utilizar expressões regulares do .NET Framework, que apresenta alta performance e todos os recursos disponíveis do dialeto, utilizando SQLCLR e C#. Está disponível desde o SQL Server 2005.
Caso você queira saber mais sobre o SQLCLR, não deixe de ler os artigos abaixo:
– Introdução ao SQL CLR (Common Language Runtime) no SQL Server
– SQL Server – Comparação de performance entre Scalar Function e CLR Scalar Function
– Outros posts sobre SQLCLR
Exemplos da fncRegex_Match
Identificando palavras repetidas
1 2 3 | SELECT CLR.dbo.fncRegex_Match('Essa frase frase contém palavras repetidas', '\b(\w+)\s+\1\b'), CLR.dbo.fncRegex_Match('Essa frase NÃO contém palavras repetidas', '\b(\w+)\s+\1\b') |
Validando uma máscara numérica específica (CEP)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SELECT -- Retorna 1 se a string atende o formato "99999-999" CLR.dbo.fncRegex_Match('29090-270', '[0-9]{5}-[0-9]{3}'), CLR.dbo.fncRegex_Match('29.090-270', '[0-9]{5}-[0-9]{3}'), CLR.dbo.fncRegex_Match('29090270', '[0-9]{5}-[0-9]{3}') SELECT -- Retorna 1 se a string atende o formato "99.999-999" CLR.dbo.fncRegex_Match('29090-270', '[0-9]{2}.[0-9]{3}-[0-9]{3}'), CLR.dbo.fncRegex_Match('29.090-270', '[0-9]{2}.[0-9]{3}-[0-9]{3}'), CLR.dbo.fncRegex_Match('29090270', '[0-9]{2}.[0-9]{3}-[0-9]{3}') SELECT -- Retorna 1 se a string atende o formato "99999999" CLR.dbo.fncRegex_Match('29090-270', '[0-9]{8}'), CLR.dbo.fncRegex_Match('29.090-270', '[0-9]{8}'), CLR.dbo.fncRegex_Match('29090270', '[0-9]{8}') |
Validando CPF e CNPJ (apenas formato, sem DV)
1 2 3 4 5 6 7 8 9 10 11 | SELECT -- Valida se o CPF informado atende a máscara "999.999.999-99" CLR.dbo.fncRegex_Match('123.456.789-09', '^\d{3}\.\d{3}\.\d{3}\-\d{2}$'), CLR.dbo.fncRegex_Match('12X.456.789-09', '^\d{3}\.\d{3}\.\d{3}\-\d{2}$'), CLR.dbo.fncRegex_Match('12345678909', '^\d{3}\.\d{3}\.\d{3}\-\d{2}$') SELECT -- Valida se o CNPJ informado atende a máscara "99.999.999/9999-99" CLR.dbo.fncRegex_Match('12.345.678/1234-09', '^\d{2}\.\d{3}\.\d{3}\/\d{4}\-\d{2}$'), CLR.dbo.fncRegex_Match('12.3X5.678/1234-09', '^\d{2}\.\d{3}\.\d{3}\/\d{4}\-\d{2}$'), CLR.dbo.fncRegex_Match('12345678123409', '^\d{2}\.\d{3}\.\d{3}\/\d{4}\-\d{2}$') |
Exemplos da fncRegex_Find
Recuperar trechos onde a palavra “Dirceu” aparece no texto
1 2 3 4 5 6 | DECLARE @exemplo VARCHAR(2000) = 'Olá pessoal! Meu nome é Dirceu Resende e quero demonstrar como o Regexp é útil nesse artigo do Dirceu blog Resende do Wordpress' SELECT '...' + Ds_Texto + '...' FROM CLR.dbo.fncRegexp_Find(@exemplo, '\bDirceu(?:\W+\w+){0,3}') |
Encontrando palavras repetidas seguidamente
1 2 3 4 | DECLARE @exemplo VARCHAR(2000) = 'nesse exemplo exemplo eu gostaria de eu demonstrar como identificar palavras palavras repetidas repetidas eu' SELECT [Ds_Texto] FROM CLR.dbo.fncRegexp_Find(@exemplo, '\b(\w+)\s+\1\b') |
Retornando um resultset com quebras de linhas (split)
1 2 3 4 5 6 7 8 | DECLARE @exemplo VARCHAR(MAX) = ' Segunda linha Terceira linha Quarta linha' SELECT Ds_Texto FROM CLR.dbo.fncRegexp_Find(@exemplo, '[^\r\n]*(?:[\r\n]*)') WHERE LEN(Ds_Texto) > 0 |
Recuperar apenas datas válidas
1 2 3 4 5 6 7 8 9 10 11 12 13 | DECLARE @exemplo VARCHAR(MAX) = ' 12/2/2006 12:30 <> 13/2/2007 32/3/2007 2-4-2007 25.8.2007 1/1/2005 34/2/2104 2/5/2006' SELECT DISTINCT CONVERT(DATETIME, Ds_Texto, 103) FROM CLR.dbo.fncRegexp_Find(@exemplo, '\b(0?[1-9]|[12][0-9]|3[01])[- /.](0?[1-9]|1[012])[- /.](19|20?[0-9]{2})\b') |
Identificando placas de veículo em uma string
1 2 3 4 5 6 7 8 | DECLARE @exemplo VARCHAR(2000) = 'Olá pessoal! Meu nome é Dirceu Resende meu carro é placa AAA-9999 e BBB9999 ou ccc2222', @mascara VARCHAR(MAX) = '[a-zA-Z]{3}-?\d{4}' SELECT * FROM CLR.dbo.fncRegexp_Find(@exemplo, @mascara) |
Exemplos da fncRegex_Replace
Removendo caracteres especiais
1 2 3 4 | DECLARE @examplo VARCHAR(MAX) = '[email protected]%ndo car$act¨eres ()_+especi*%#!ais no meu texto:^{}<>|\' SELECT CLR.dbo.fncRegex_Replace(@examplo, '[^a-zA-Z0-9 ]', '') |
Removendo tags HTML
1 2 3 4 5 | DECLARE @exemplo VARCHAR(MAX) SET @exemplo = '<span class="Teste">Utilizando</span> <strong>RegExp</strong> para <u>remover</u> códigos HTML <!-- (até comentários) -->. Veja mais no <a href="https://dirceuresende.com/blog">meu blog</a>' SELECT CLR.dbo.fncRegex_Replace(@exemplo, '<(?:[^>''"]*|([''"]).*?\1)*>', '') |
Converter dados tabulares para comando de INSERT no banco
1 2 3 4 5 6 7 8 9 10 11 12 | DECLARE @exemplo VARCHAR(MAX) = '1|Dirceu|154 2|Resende|467 3|Blog|1348 4|SQL Server|139 ' PRINT CLR.dbo.fncRegex_Replace( @exemplo, '([^\|\r\n]+)[|\r\n]+([^\|\r\n]+)[|\r\n]+([^\|\r\n]+)[|\r\n]+', 'INSERT INTO Tabela (Coluna1, Coluna2, Coluna3) VALUES ($1, ''$2'', $3); '); |
Remover palavras duplicadas
1 2 3 4 | DECLARE @examplo VARCHAR(MAX) = 'RegExp para remover remover palavras duplicadas duplicadas duplicadas' SELECT CLR.dbo.fncRegex_Replace(@examplo, '\b(\w+)(?:\s+\1\b)+', '$1') |
Código-fonte C# das funções
Caso você queira utilizar essas funções no seu próprio projeto SQLCLR, pode utilizar os códigos-fonte disponibilizados abaixo:
fncRegex_Replace
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | using System; using System.Data.SqlTypes; using System.Text.RegularExpressions; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString fncRegex_Replace(SqlString Ds_Texto, SqlString Ds_Mascara, SqlString Ds_Substituir) { if (Ds_Texto.IsNull || Ds_Mascara.IsNull || Ds_Substituir.IsNull) return SqlString.Null; try { return Regex.Replace(Ds_Texto.Value, Ds_Mascara.Value, Ds_Substituir.Value); } catch (Exception e) { return SqlString.Null; } } } |
fncRegexp_Match
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | using System; using System.Data.SqlTypes; using System.Text.RegularExpressions; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString fncRegex_Match(SqlString Ds_Texto, SqlString Ds_Mascara) { if (Ds_Texto.IsNull || Ds_Mascara.IsNull) return SqlString.Null; try { var resultado = Regex.Match(Ds_Texto.Value, Ds_Mascara.Value); return resultado.Success ? resultado.Groups[0].Value : SqlString.Null; } catch (Exception e) { return SqlString.Null; } } } |
fncRegexp_Find
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | using System.Collections; using System.Data.SqlTypes; using System.Text.RegularExpressions; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions { private class RegexpFind { public SqlInt32 Nr_Linha; public SqlString Ds_Texto; public RegexpFind(SqlInt32 nrLinha, SqlString dsTexto) { Nr_Linha = nrLinha; Ds_Texto = dsTexto; } } [Microsoft.SqlServer.Server.SqlFunction( FillRowMethodName = "FillRow_Regexp_Find", TableDefinition = "Nr_Linha INT, Ds_Texto NVARCHAR(MAX)", DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read )] public static IEnumerable fncRegexp_Find(string Ds_Texto, string Ds_Mascara) { var regexpFindCollection = new ArrayList(); if (string.IsNullOrEmpty(Ds_Texto) || string.IsNullOrEmpty(Ds_Mascara)) return regexpFindCollection; var contador = 1; var retorno = Regex.Matches(Ds_Texto, Ds_Mascara, RegexOptions.None); foreach (var linha in retorno) { regexpFindCollection.Add(new RegexpFind( contador, linha.ToString() )); contador++; } return regexpFindCollection; } protected static void FillRow_Regexp_Find(object objRegexpFind, out SqlInt32 nrLinha, out SqlString dsTexto) { var regexpFind = (RegexpFind) objRegexpFind; nrLinha = regexpFind.Nr_Linha; dsTexto = regexpFind.Ds_Texto; } } |
Código-fonte T-SQL das funções
Caso você não tenha muita familiaridade com C# ou projetos no Visual Studio, mas mesmo assim quer utilizar expressões regulares no SQL Server, vou disponibilizar o código T-SQL abaixo, que vai te permitir criar o assembly e as funções no seu banco de dados, sem ter muito esforço, apenas apertando “F5” nesse script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | CREATE ASSEMBLY [Regexp] FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103008C57115B0000000000000000E00022200B013000000E00000006000000000000BE2C00000020000000400000000000100020000000020000040000000000000006000000000000000080000000020000000000000300608500001000001000000000100000100000000000001000000000000000000000006C2C00004F00000000400000A002000000000000000000000000000000000000006000000C000000342B00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000C40C000000200000000E000000020000000000000000000000000000200000602E72737263000000A0020000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000A02C00000000000048000000020005004C220000E808000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3002006A00000001000011000F00280500000A2D090F01280500000A2B01170A062C087E0600000A0B2B4800000F00280700000A0F01280700000A280800000A0C086F0900000A2D077E0600000A2B16086F0A00000A166F0B00000A6F0C00000A280D00000A0BDE0A0D007E0600000A0BDE00072A000001100000000021003D5E000A090000011B3003005900000002000011000F00280500000A2D120F01280500000A2D090F02280500000A2B01170A062C087E0600000A0B2B2E00000F00280700000A0F01280700000A0F02280700000A280E00000A280D00000A0BDE0A0C007E0600000A0BDE00072A0000000110000000002A00234D000A090000011B3003008D0000000300001100730F00000A0A02281000000A2D0803281000000A2B01170D092C050613042B69170B020316281100000A0C00086F1200000A13052B2D11056F1300000A1306000607281400000A11066F1500000A280D00000A73060000066F1600000A260717580B0011056F1700000A2DCADE1611057510000001130711072C0811076F1800000A00DC0613042B0011042A00000001100000020035003A6F001600000000133002002100000004000011000274030000020A03067B01000004811100000104067B0200000481070000012A2202281900000A002A5E02281900000A000002037D0100000402047D020000042A000042534A4201000100000000000C00000076342E302E33303331390000000005006C000000C8020000237E0000340300002403000023537472696E6773000000005806000004000000235553005C0600001000000023475549440000006C0600007C02000023426C6F620000000000000002000001571502000902000000FA01330016000001000000170000000300000002000000060000000C00000019000000060000000400000001000000030000000100000000005A010100000000000600F00024020600100124020600C70011020F00440200000600DB0279010A00DB00C8010A0038015B020E00540170020600A00179010A007F00C8010A007900C80106009F008F020600FA028F020E00800170020600F1018F020600AB0079010A0001005B020E000D0370020E00BB0170020E00900170020E00B70070020600440179010E00B7027002000000000A00000000000100010001001000A2020000150001000100030010006E000000150001000600060013008D000600AA011E0050200000000096004B0191000100D8200000000096008E009A00030050210000000096005C00A5000600FC210000000094004800AC00080029220000000086180B0206000B0032220000000086180B02B7000B0000000100AA0100000200240000000100AA0100000200240000000300E30100000100AA01000002002400000001006B00020002001C0002000300B301000001001C0000000200B30109000B02010011000B02060019000B020A0031000B020600390065011A0039006B011E0039002E0122009100540126009900CF021A004100C4022D00A10070013200A9002E0122003900E202380091009700460069000B020600B10013035D009100530262007100FD016B007900EE0270008900E2027400290042012200690044007A00790004031A008100BF00060029000B02060020002300F0002E000B00BF002E001300C8002E001B00E70040002300F00060002300F50010003E004D007F00048000000000000000000000000000000000C101000004000000000000000000000084003B000000000004000000000000000000000084002F000000000004000000000000000000000084007901000000000300020000000053716C496E743332003C4D6F64756C653E004E725F4C696E6861006E724C696E68610044735F4D6173636172610053797374656D2E44617461006D73636F726C6962004164640046696C6C526F775F5265676578705F46696E6400666E635265676578705F46696E64006F626A52656765787046696E640053797374656D446174614163636573734B696E6400666E6352656765785F5265706C6163650049456E756D657261626C650049446973706F7361626C65004361707475726500446973706F73650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650053716C537472696E6700546F537472696E6700666E6352656765785F4D61746368005265676578702E646C6C006765745F49734E756C6C006765745F4974656D0053797374656D004D61746368436F6C6C656374696F6E0047726F7570436F6C6C656374696F6E00457863657074696F6E0044735F546578746F006473546578746F0047726F757000526567657870004D6963726F736F66742E53716C5365727665722E5365727665720044735F537562737469747569720049456E756D657261746F7200476574456E756D657261746F72002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F646573004D6174636865730053797374656D2E446174612E53716C54797065730053797374656D2E546578742E526567756C617245787072657373696F6E730053797374656D2E436F6C6C656374696F6E730055736572446566696E656446756E6374696F6E730052656765784F7074696F6E73006765745F47726F757073006765745F53756363657373004F626A656374006F705F496D706C69636974006765745F43757272656E740041727261794C697374004D6F76654E6578740052656765780049734E756C6C4F72456D70747900000000000000000C14EAF7F46A2245B903BE327055CEBE0004200101080320000105200101111109070402111D12211225032000020306111D0320000E06000212210E0E0420001251052001124D08050001111D0E07070302111D12250600030E0E0E0E0F07081235081239021231123D1C1241040001020E08000312390E0E115D042000123D0320001C050001114508042001081C040701120C08B77A5C561934E08903061145080002111D111D111D0A0003111D111D111D111D06000212310E0E0A0003011C10114510111D072002011145111D0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000000401000000818501000400540E1146696C6C526F774D6574686F644E616D651346696C6C526F775F5265676578705F46696E64540E0F5461626C65446566696E6974696F6E244E725F4C696E686120494E542C2044735F546578746F204E56415243484152284D4158295455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730100000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D4461746141636365737301000000000000008C57115B00000000020000001C010000502B0000500D000052534453A6F053CD17E4E94A923D821619E3D25F01000000433A5C55736572735C646966696C5C446F63756D656E74735C56697375616C2053747564696F20323031375C50726F6A656374735C5265676578705C5265676578705C6F626A5C44656275675C5265676578702E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000942C00000000000000000000AE2C0000002000000000000000000000000000000000000000000000A02C0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000440200000000000000000000440234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004A4010000010053007400720069006E006700460069006C00650049006E0066006F0000008001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000036000B00010049006E007400650072006E0061006C004E0061006D00650000005200650067006500780070002E0064006C006C00000000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000003E000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005200650067006500780070002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000C03C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WITH PERMISSION_SET = SAFE GO CREATE FUNCTION [dbo].[fncRegex_Match] (@Ds_Texto [nvarchar](MAX), @Ds_Mascara [nvarchar](MAX)) RETURNS [nvarchar](MAX) AS EXTERNAL NAME [Regexp].[UserDefinedFunctions].[fncRegex_Match]; GO CREATE FUNCTION [dbo].[fncRegex_Replace] (@Ds_Texto [nvarchar](MAX), @Ds_Mascara [nvarchar](MAX), @Ds_Substituir [nvarchar](MAX)) RETURNS [nvarchar](MAX) AS EXTERNAL NAME [Regexp].[UserDefinedFunctions].[fncRegex_Replace]; GO CREATE FUNCTION [dbo].[fncRegexp_Find](@Ds_Texto [nvarchar](max), @Ds_Mascara [nvarchar](max)) RETURNS TABLE ( [Nr_Linha] [int] NULL, [Ds_Texto] [nvarchar](max) NULL ) WITH EXECUTE AS CALLER AS EXTERNAL NAME [Regexp].[UserDefinedFunctions].[fncRegexp_Find] GO |
Alternativa #3: Dialeto ECMA com OLE Automation e VBScript
Utilizando o método do OLE Automation, você terá acesso à todos os recursos do dialeto ECMA no seu SQL Server, e poderá criar expressões regulares bem complexas. O custo disso, é que o uso do OLE Automation pode deixar a execução do seu código mais lento que as outras opções, mas a sua implementação é mais simples que a alternativa utilizando SQLCLR.
Disponível desde a versão 2000, o OLE Automation pode causar algumas instabilidades na sua instância devido à conhecidos problemas de gerenciamento de memória pelo SQL Server, um dos motivos que levou a indicação de substituição desse recurso pelo SQLCLR. O recado é: Use com moderação.
Quer saber mais sobre o OLE Automation? Não deixe de ler esses artigos abaixo:
– Habilitando OLE Automation via T-SQL no SQL Server
– Operações com arquivos utilizando OLE Automation no SQL Server
– Outros posts sobre OLE Automation
Como ativar o OLE Automation
Para ativar o OLE Automation na sua instância, basta executar o comando abaixo:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO sp_configure 'Agent XPs', 1; GO RECONFIGURE; GO sp_configure 'show advanced options', 1; GO RECONFIGURE; GO |
Para conhecer meus outros artigos sobre OLE Automation, acesse este link aqui.
Para esse tópico, vou utilizar as excelentes funções do Phil Factor RegexReplace, RegexMatch e RegexFind (código fonte após os exemplos).
Exemplos de uso da RegexMatch
Identificar palavras repetidas
1 2 3 | SELECT dbo.RegexMatch('\b(\w+)\s+\1\b','Essa frase frase contém palavras repetidas'), dbo.RegexMatch('\b(\w+)\s+\1\b','Essa frase NÃO contém palavras repetidas') |
Identificar palavras próximas
1 2 3 4 5 6 7 | -- Verifica se na frase, a palavra "Resende" está de 1 até 6 palavras após a palavra "Dirceu" SELECT dbo.RegexMatch('\bDirceu(?:\W+\w+){1,6}?\W+Resende\b', 'Meu nome é Dirceu e meu sobrenome é Resende. Trabalho com BI e SQL Server') -- Verifica se na frase, a palavra "Resende" está de 1 até 2 palavras após a palavra "Dirceu" SELECT dbo.RegexMatch('\bDirceu(?:\W+\w+){1,2}?\W+Resende\b', 'Meu nome é Dirceu e meu sobrenome é Resende. Trabalho com BI e SQL Server') |
Validando uma máscara numérica específica (CEP)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SELECT -- Retorna 1 se a string atende o formato "99999-999" dbo.RegexMatch('[0-9]{5}-[0-9]{3}', '29090-270'), dbo.RegexMatch('[0-9]{5}-[0-9]{3}', '29.090-270'), dbo.RegexMatch('[0-9]{5}-[0-9]{3}', '29090270') SELECT -- Retorna 1 se a string atende o formato "99.999-999" dbo.RegexMatch('[0-9]{2}.[0-9]{3}-[0-9]{3}', '29090-270'), dbo.RegexMatch('[0-9]{2}.[0-9]{3}-[0-9]{3}', '29.090-270'), dbo.RegexMatch('[0-9]{2}.[0-9]{3}-[0-9]{3}', '29090270') SELECT -- Retorna 1 se a string atende o formato "99999999" dbo.RegexMatch('[0-9]{8}', '29090-270'), dbo.RegexMatch('[0-9]{8}', '29.090-270'), dbo.RegexMatch('[0-9]{8}', '29090270') |
Validando CPF e CNPJ (apenas formato, sem DV)
1 2 3 4 5 6 7 8 9 10 11 | SELECT -- Valida se o CPF informado atende a máscara "999.999.999-99" dbo.RegexMatch('^\d{3}\.\d{3}\.\d{3}\-\d{2}$','123.456.789-09'), dbo.RegexMatch('^\d{3}\.\d{3}\.\d{3}\-\d{2}$','12X.456.789-09'), dbo.RegexMatch('^\d{3}\.\d{3}\.\d{3}\-\d{2}$','12345678909') SELECT -- Valida se o CNPJ informado atende a máscara "99.999.999/9999-99" dbo.RegexMatch('^\d{2}\.\d{3}\.\d{3}\/\d{4}\-\d{2}$','12.345.678/1234-09'), dbo.RegexMatch('^\d{2}\.\d{3}\.\d{3}\/\d{4}\-\d{2}$','12.3X5.678/1234-09'), dbo.RegexMatch('^\d{2}\.\d{3}\.\d{3}\/\d{4}\-\d{2}$','12345678123409') |
Exemplos de uso da RegexFind
Recuperar trechos onde a palavra “Dirceu” aparece no texto
1 2 3 4 5 6 | DECLARE @exemplo VARCHAR(2000) = 'Olá pessoal! Meu nome é Dirceu Resende e quero demonstrar como o Regexp é útil nesse artigo do Dirceu Resende blog do Wordpress' SELECT '...' + SUBSTRING(@exemplo, FirstIndex - 8, Length + 16) + '...' FROM dbo.RegexFind('\bDirceu(?:\W+\w+){0,3}', @exemplo, 1, 1) |
Encontrando palavras repetidas seguidamente
1 2 3 4 | -- encontrando palavras repetidas seguidamente DECLARE @exemplo VARCHAR(2000) = 'nesse exemplo exemplo eu gostaria de eu demonstrar como identificar palavras palavras repetidas repetidas eu' SELECT [Value] FROM dbo.RegexFind ('\b(\w+)\s+\1\b', @exemplo, 1, 1) |
Retornando um resultset com quebras de linhas (split)
1 2 3 4 5 6 7 8 | DECLARE @exemplo VARCHAR(MAX) = ' Segunda linha Terceira linha Quarta linha' SELECT Value FROM dbo.RegexFind('[^\r\n]*(?:[\r\n]*)', @exemplo,1,1) WHERE [Length] > 0 |
Quebrar linhas para cada palavra da frase (split)
1 2 3 4 | DECLARE @exemplo VARCHAR(MAX) = 'Com essa função, cada palavra vai ficar em uma linha do resultado' SELECT Value FROM dbo.RegexFind ('\b[\w]+\b', @exemplo,1,1) |
Recuperar apenas datas válidas
1 2 3 4 5 6 7 8 9 10 11 12 13 | DECLARE @exemplo VARCHAR(MAX) = ' 12/2/2006 12:30 <> 13/2/2007 32/3/2007 2-4-2007 25.8.2007 1/1/2005 34/2/2104 2/5/2006' SELECT DISTINCT CONVERT(DATETIME, Value, 103) FROM dbo.RegexFind ('\b(0?[1-9]|[12][0-9]|3[01])[- /.](0?[1-9]|1[012])[- /.](19|20?[0-9]{2})\b', @exemplo, 1, 1) |
Identificando placas de veículo em uma string
1 2 3 4 5 6 7 8 | DECLARE @exemplo VARCHAR(2000) = 'Olá pessoal! Meu nome é Dirceu Resende meu carro é placa AAA-9999 e BBB9999 ou ccc2222', @mascara VARCHAR(MAX) = '[a-zA-Z]{3}-?\d{4}' SELECT [Value] FROM dirceuresende.dbo.RegexFind(@mascara, @exemplo, 1, 1) |
Exemplos de uso da RegexReplace
Identifica URL e encapsula como link html – tag A
1 2 3 4 5 6 | SELECT dbo.RegexReplace( '\b(https?|ftp|file)://([-A-Z0-9+&@#/%?=~_|!:,.;]*[-A-Z0-9+&@#/%=~_|])', '<a href="$2">$2</a>', 'Testando Regexp com o site do https://www.google.com.br', 1, 1 ) |
Remover strings HTML de um texto
1 2 3 4 5 | DECLARE @exemplo VARCHAR(MAX) SET @exemplo = '<span class="Teste">Utilizando</span> <strong>RegExp</strong> para <u>remover</u> códigos HTML <!-- (até comentários) -->. Veja mais no <a href="https://dirceuresende.com/blog">meu blog</a>' SELECT dbo.RegexReplace('<(?:[^>''"]*|([''"]).*?\1)*>', '', @exemplo, 1, 1) |
Converter dados tabulares para comando de INSERT no banco
1 2 3 4 5 6 7 8 9 10 11 12 13 | DECLARE @exemplo VARCHAR(MAX) = '1|Dirceu|154 2|Resende|467 3|Blog|1348 4|SQL Server|139 ' PRINT dbo.RegexReplace( '([^\|\r\n]+)[|\r\n]+([^\|\r\n]+)[|\r\n]+([^\|\r\n]+)[|\r\n]+', 'INSERT INTO Tabela (Coluna1, Coluna2, Coluna3) VALUES ($1, ''$2'', $3); ', @exemplo, 1, 1 ); |
Remover palavras duplicadas
1 2 3 4 | DECLARE @examplo VARCHAR(MAX) = 'RegExp para remover remover palavras duplicadas duplicadas duplicadas' SELECT dbo.RegexReplace('\b(\w+)(?:\s+\1\b)+', '$1', @examplo, 1, 1) |
Removendo caracteres especiais
1 2 3 4 | DECLARE @examplo VARCHAR(MAX) = '[email protected]%ndo car$act¨eres ()_+especi*%#!ais no meu texto:^{}<>|\' SELECT dbo.RegexReplace('[^a-zA-Z0-9 ]', '', @examplo, 1, 1) |
Código da função RegexFind
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 |