Split de Lista no SQL Server

 

Noutro dia precisei separar uma lista de valores separados por vírgula em um procedimento do SQL Server 2019. Essa lista de valores era passada via parâmetro da Stored Procedure.

Como não encontrei um tipo de dados adequado no SQL Server, então resolvi implementar uma função que retornasse uma lista com separador opcional.

Quem sabe um dia a Microsoft não implemente algo, como já existe no PostgreSQL, e pelo que me lembro, desde sua versão 7.1. [0]

A partir do SQL Server 2005 podemos utilizar o XML, por exemplo, contudo não vi a necessidade de sobrecarga se poderia utilizar algo mais simples e eficiente. Essa solução serve para vários tipos de estruturas e pode ser útil para relatórios mais complexos.

A função de Split da lista do SQL Server ficou bem para a minha necessidade. Talvez tu possas ter uma variação ou melhoria no código. Segue o caso de teste:

Parâmetro de Entrada: lista (1,2,3,4,5,6,7) Retorno esperado: lista 1 2 3 4 5 6 7 Para isso vou passar a lista como uma variável do tipo varchar e vou incluir um separador ',' (vírgula). O retorno será em uma @listaTabela, do tipo table.

create function dbo.fg_split (@lista varchar(max), @separador varchar(max) = ',')   returns @listaTabela table (lista varchar(max))   -- Return Table [1]   -- @listaTabela = Variável do tipo table que receberá o resultado   as begin      -- Variáveis que serão utilizadas      -- @posicao = Posição do separador na lista      -- @anterior = Posição inicial para percorrer a lista      declare @posicao int, @anterior int         set @lista = @lista + @separador      set @anterior = 1      set @posicao = charindex(@separador, @lista)      -- charindex [2]         while @posicao > 0      begin       if @posicao - @anterior > 0        insert into @listaTabela values (substring(@lista, @anterior, @posicao - @anterior))          if @posicao >= len(@lista) break        set @anterior = @posicao + 1          set @posicao = charindex(@separador, @lista, @anterior)      end      return   end   GO      select * from dbo.fg_split('1,2,3,4,5,6,7',',')   GO

 

Por quê utilizar esta técnica? Performance!

 

Aqui é feita uma chamada para o banco de dados.

 

Para os “puristas” do design pattern, pergunto: Por quê não seria uma boa ideia passar uma coleção para um procedimento armazenado? Se for uma solução ruim, então, é uma má ideia no .NET passar uma coleção como argumento para um método!

 

 

 

[0] https://www.postgresql.org/docs/12/arrays.html

[1] https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/how-to-use-table-valued-user-defined-functions

[2] https://docs.microsoft.com/en-us/sql/t-sql/functions/charindex-transact-sql?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev15.query%3FappId%3DDev15IDEF1%26l%3DEN-US%26k%3Dk(charindex_TSQL);k(sql13.swb.tsqlresults.f1);k(sql13.swb.tsqlquery.f1);k(MiscellaneousFilesProject);k(DevLang-TSQL)%26rd%3Dtrue&view=sql-server-ver15

Anderson Abreu

Métodos para validar email no SQL Server 2019!

 

Deixo um plus para validar email em base de dados (Oracle, SQL Server, Postgres, MySQL...) na criação da tabela.

A programação tem evoluído numa velocidade incrível, entretanto os padrões ANSI do SQL têm sido mais conservadores, veja só, existem linguagens de programação que já fazem essa validação na tipagem de dados, contudo quando falamos de padrão ANSI para SQL, e ai “o buraco é mais embaixo”.

Então, encontrei duas formas simples de fazer isso, cada uma tem sua característica específica. Veja:

1ª Forma: Função

Isso mesmo, por questões de validação de parâmetros que eram passados na chamada de uma stored procedure, precisei validar um parâmetro e-mail.

Por quê? Uma questão pontual!

/* Funcionalidade: Validar o formato de e-mails no SQL Criado em: 01/05/2010 Criado por: Anderson Abreu Descrição: Validar parâmetro email como variável ou Validando campos de email cadastrados em uma tabela Regras: */ use master GO if db_id('tst_function_email') is not null      drop database tst_function_email go create database tst_function_email go use tst_function_email go create or alter function dbo.fg_verifica_email ( @email varchar(150) ) returns varchar(20) as begin      declare @valid varchar(20)     if @email is not null     begin         set @email = lower(@email)         set @valid = 'Não é válido'         if @email like '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z]%'              and @email not like '%@%@%'              and charindex('.@',@email) = 0              and charindex('..',@email) = 0              and charindex(',',@email) = 0              and charindex('''',@email) = 0              and charindex('^',@email) = 0              and charindex(']',@email) = 0              and charindex('%',@email) = 0              and right(@email,1) not like '.'         begin             set @valid='Válido'         end     end     return @valid end go -- Vamos Testar -- FORMATOS NÃO PERMITIDOS -- começando com @ select dbo.fg_verifica_email('@dominio.com') -- sem sufixo de domínio select dbo.fg_verifica_email('anderson@dominio') -- espaço select dbo.fg_verifica_email('po ker@dominio') -- aspas select dbo.fg_verifica_email('po"ker@dominio') -- caracteres especiais select dbo.fg_verifica_email('po$ker@dominio') select dbo.fg_verifica_email('anderson^pokemon@dominio.com') select dbo.fg_verifica_email('anderson]pokemon@dominio.com') select dbo.fg_verifica_email('anderson%pokemon@dominio.com') -- @. juntos select dbo.fg_verifica_email('anderson@.dominio.com') -- 2 arrobas juntos select dbo.fg_verifica_email('anderson@@dominio.com') -- 2 arrobas separados select dbo.fg_verifica_email('anderson@dominio@dominio.com') -- .@ juntos select dbo.fg_verifica_email('anderson.@dominio.com') -- .. juntos select dbo.fg_verifica_email('anderson@dominio..com') -- . no final select dbo.fg_verifica_email('anderson@dominio.ad.') -- FORMATOS PERMITIDOS select dbo.fg_verifica_email('anderson@dominio.com.br') select dbo.fg_verifica_email('anderson@dominio.com') select dbo.fg_verifica_email('anderson.abreu@dominio.com') select dbo.fg_verifica_email('anderson_abreu@dominio.com') select dbo.fg_verifica_email('anderson-abreu@dominio.com') select dbo.fg_verifica_email('anderson@sistemaexpresso.net.br') select dbo.fg_verifica_email('anderson193@dominio.com') select dbo.fg_verifica_email('2020@2020.net.br') -- Excluindo dados de teste use master go drop database tst_ function_email go

 

2ª Forma: Constraints na tabela

São criadas constraints na tabela, com validação REGEX. Contudo, nesse cenário, o dado é validado no momento que é persistido na tabela. Veja um código bem bacana:

 

 

 

/* Funcionalidade: Validar o formato de e-mails no SQL Criado em: 01/05/2010 Criado por: Anderson Abreu Descrição: Validar persistência de dados de email em tabela com check constraints Regras: ck_rule_email_01      exige formato:      [letras-numeros-underline-traço] +      [qualquer coisa] +      [@] +      [letras-numeros-underline-traço] +      [qualquer coisa] +      [.] +      [letras] +      qualquer coisa ck_rule_email_02      impede caracteres que não sejam: a-z 0-9 @ . _ - ck_rule_email_03      impede dois arrobas ck_rule_email_04      impede .@ ck_rule_email_05      impede .. ck_rule_email_06      impede terminar com . */ use master GO if db_id('tst_constraint_email') is not null      drop database tst_constraint_email go create database tst_constraint_email go use tst_constraint_email go if object_id('tb_pessoa') is not null      drop table tb_pessoa create table tb_pessoa (      pk_pessoa int identity primary key,      no_pessoa varchar(150),      cd_email varchar(150),      constraint ck_rule_email_01 check (cd_email like '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z]%'),      constraint ck_rule_email_02 check (cd_email not like '%[^a-z0-9@._-]%'),      constraint ck_rule_email_03 check (cd_email not like '%@%@%'),      constraint ck_rule_email_04 check (cd_email not like '%.@%'),      constraint ck_rule_email_05 check (cd_email not like '%..%'),      constraint ck_rule_email_06 check (cd_email not like '%.') ) -- Vamos Testar -- FORMATOS NÃO PERMITIDOS -- começando com @ insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', '@dominio.com') -- sem sufixo de domínio insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson@dominio') -- espaço insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'po ker@dominio') -- aspas insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'po"ker@dominio') -- caracteres especiais insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'po$ker@dominio') insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson^pokemon@dominio.com') insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson]pokemon@dominio.com') insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson%pokemon@dominio.com') -- @. juntos insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson@.dominio.com') -- 2 arrobas juntos insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson@@dominio.com') -- 2 arrobas separados insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson@dominio@dominio.com') -- .@ juntos insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson.@dominio.com') -- .. juntos insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson@dominio..com') -- . no final insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson@dominio.ad.') -- FORMATOS PERMITIDOS insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson@dominio.com.br') insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson@dominio.com') insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson.abreu@dominio.com') insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson_abreu@dominio.com') insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson-abreu@dominio.com') insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson@sistemaexpresso.net.br') insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', 'anderson193@dominio.com') insert into tb_pessoa (no_pessoa, cd_email) values ('Anderson Abreu', '2020@2020.net.br') -- Excluindo dados de teste use master go drop database tst_constraint_email go

Anderson Abreu

Validar mês e ano SQL Server 2019!

 

Uma maneira simples e eficiente de validar parâmetros passados em consultas, stored procedures, funções.

Vez o outro precisamos de uma forma de validar parâmetros de mês e ano, ou ainda limitar os ranges de utilização. Segue uma maneira simples para fazermos isso.

Então, sem muitas delongas. Veja:

/* Funcionalidade: Validar o mês no SQL Criado em: 18/05/2010 Criado por: Anderson Abreu Descrição: Validar parâmetro mes como variável Regras: */ use master GO if db_id('tst_database') is not null drop database tst_database go create database tst_database go use tst_database go CREATE FUNCTION [dbo].[fg_verifica_ano] ( @ano int ) RETURNS bit as BEGIN     DECLARE @valido bit     IF @ano IS NOT NULL     BEGIN         SET @valido = 0         IF len(@ano) = 4 AND @ano >= 2004 and @ano <= 2200             BEGIN                 SET @valido=1             END     END     RETURN @valido END go CREATE FUNCTION [dbo].[fg_verifica_mes] ( @mes int ) RETURNS bit as BEGIN     DECLARE @valido bit     IF @mes IS NOT NULL     BEGIN         SET @valido = 0         IF @mes between 1 and 12         BEGIN             SET @valido=1         END     END     RETURN @valido END go -- Vamos Testar select dbo.fg_verifica_ano(2020) select dbo.fg_verifica_ano(202) -- Erro select dbo.fg_verifica_mes(12) select dbo.fg_verifica_mes(13) -- Erro create procedure proc_mes_ano (@mes tinyint, @ano smallint) as begin     set nocount on     set dateformat YMD     declare @checkError bit = 0, @dtInicio varchar(20), @dtFim varchar(20);     if OBJECT_ID('tempdb.dbo.#temp_error', 'U') is not null         drop table #temp_error;     create table #temp_error ( name varchar(255) );     IF dbo.fg_verifica_ano(@ano) = 0     begin         insert into #temp_error values ('Ano inválido')         set @checkError = 1     end     IF @mes is not null         IF dbo.fg_verifica_mes(@mes) = 0         begin             insert into #temp_error values ('Mês inválido')             set @checkError = 1         end     if @checkError = 1         Select * from #temp_error     else     begin         if OBJECT_ID('tempdb.dbo.#temp_all', 'U') IS NOT NULL             drop table #temp_all;         if @mes = 1             set @dtInicio = cast(cast(@ano-1 as char(4)) +'-'+ cast((12) as varchar(2)) +'-1 00:00' as datetime)         else             set @dtInicio = cast(cast(@ano as char(4)) +'-'+ cast((@mes-1) as varchar(2)) +'-2 00:00' as datetime)         set @dtFim = cast(cast(@ano as char(4)) +'-'+ cast((@mes) as varchar(2)) +'-21 00:00' as datetime)         select * from sys.databases where create_date between @dtInicio and @dtFim     end     set nocount off end declare @mes tinyint = month(getdate()), @ano smallint = year(getdate()) exec proc_mes_ano @mes, @ano GO --Produzindo o erro exec proc_mes_ano 13, 2020 GO exec proc_mes_ano 11, 202 GO exec proc_mes_ano 0, 202 GO -- Excluindo dados de teste use master go drop database tst_database go

 

Anderson Abreu

Por quê os desenvolvedores odeiam PHP

PHP é a linguagem mais usada no mundo para sites. É também o mais odiado. E é puro ódio. Mas por que tantos desenvolvedores odeiam tanto? Hoje estamos indo para a origem do ódio, vamos ver se é realmente justificado.

 

Não toque no meu PHP

Se estou dizendo que o PHP é odiado pelo mundo inteiro, não é minha opinião pessoal. Na mais recente pesquisa do StackOverflow, desenvolvedores de todo o mundo colocaram o PHP entre as 5 principais linguagens mais temidas. A última pesquisa contratada coloca-o entre os 2 mais odiados. Há muito mais e nem estou falando sobre o que está acontecendo no Reddit. Se você é desenvolvedor de PHP há 20 anos e veio aqui em uma cruzada para me crucificar no altar do Symfony, relaxe. Estamos aqui para conversar, abaixe a arma. Bem, agora, pelo contrário, se você odeia o PHP com paixão: pegue algumas pipocas e sente-se confortavelmente.

Era uma vez eu mesmo, estava fazendo o Symfony2. E gostei da coisa toda do Symfony, francamente, não é ruim. Eu disse isso a um desenvolvedor que conheci em um encontro de devops. Ele ficou chocado, estava prestes a vomitar, olhou para mim como se eu tivesse acabado de matar um bebê recém-nascido. De agora em diante, vou chamá-lo de James para facilitar. Então eu imediatamente fiz uma pergunta a James: por que tanto ódio?

 

Passado conturbado do PHP

Para entender o que está por vir, você precisa do contexto. Para fazer isso, vamos retroceder um pouco. Estamos em 1994 e Rasmus Lerdorf está codificando rapidamente um conjunto de ferramentas C. Este conjunto de ferramentas é usado para gerenciar seu site pessoal. Rasmus está fazendo isso apenas por sua própria necessidade no momento. Ele não quer transformá-lo em uma linguagem de programação. Nada neste conjunto de ferramentas foi projetado para se tornar uma linguagem. Um ano depois, Rasmus lançou este kit de ferramentas de código aberto chamado então: PHP/FI. E parecia assim.

Estamos longe do que você está usando hoje em sua enorme estrutura PHP7, não estamos? No entanto, tudo vem de lá. Para o resto da história: o código é assumido por uma equipe de desenvolvimento e o PHP / FI2 é lançado em 1997. Em 1998, uma parte é reescrita e o PHP3 é lançado. Em 1999, o núcleo do PHP foi totalmente reescrito para produzir o Zend. O PHP4 segue o próximo ano e o PHP5 é lançado em 2004 com o Zend2. O PHP6 foi cancelado porque o desenvolvimento se transformou em um inferno na Terra. Finalmente, através de outra grande reescrita da linguagem: o PHP7 foi lançado em 2015. Lembro-me de que estava muito empolgado. Mas originalmente, Rasmus estava apenas codificando suas próprias coisas. Em 2003, ele fez uma entrevista e contou coisas incríveis.

“I really don’t like programming. I built this tool to program less so that I could simply reuse code … I don’t know how to stop it, there was never any intention to write a programming language […]. I don’t know how to write a programming language at all, I just kept adding the next logical step.”

 

Inconsistência e design deficiente

Em 2019, com o nosso amigo James do encontro. Ele imediatamente começou a falar comigo sobre a sintaxe "nojenta" do PHP. Como o idioma não é digitado. Como o idioma é principalmente imprevisível. Seja no uso de operadores ou nas incríveis inconsistências na nomeação de funções. Pouparei a lista de problemas técnicos, pois, caso contrário, este artigo levará o dia todo para você ler.

Se você realmente quer saber tudo, achei a pessoa mais irritada do mundo contra o PHP. Não, na verdade acho que a pessoa que mais odeia o PHP é na verdade ele. Todas essas postagens validam o que James estava me dizendo. Os desenvolvedores odeiam o PHP porque é uma linguagem tecnicamente inconsistente com um design ruim. Quando você o compara com outras linguagens, o contraste é óbvio. Se depois de ler esses dois artigos, você não pode admitir que o PHP tem alguns problemas, é porque se tornou sua religião. E eu pararei de tentar convencer fanáticos.

Usando o PHP, é muito fácil produzir código incorreto. Outros idiomas têm muito mais restrições. Também vemos muitos problemas de segurança com o PHP. E o problema é o mesmo aqui. É muito fácil criar código ruim e inseguro com PHP. Mas o PHP não é uma brecha na segurança ou está fadado ao código feio se você codificar corretamente. Os desenvolvedores odeiam o PHP porque é mais provável que você obtenha erros com uma linguagem que permita tanta liberdade.

 

Vítima do seu sucesso

Há outro fator importante nesse ódio ambiental. Os desenvolvedores odeiam o PHP porque ele é usado em qualquer lugar. Este blog é executado em PHP7. Como a maioria da web, eu só preciso hospedar uma página dinâmica que é armazenada em cache. PHP faz o trabalho. Sim, o idioma em si não é o melhor. Sim, eu poderia usar uma pilha super duper. Mas quem se importa? Atende às minhas necessidades de forma rápida e acessível. É também por esse motivo que 80% da web é executada em PHP.

Quando expliquei o mesmo a James, ele começou a enlouquecer. Como você e eu, James tem amigos e família. Essas pessoas freqüentemente pedem ajuda a ele em seus sites profissionais e pessoais. Adivinhe qual idioma sempre aparece nos sites dessas pessoas? Então, James se vê tendo que mudar as coisas no PHP frequentemente. E ele tem uma dor de cabeça incrível com isso.

Com o advento do WordPress, que literalmente domina a Internet, James joga com PHP por um longo tempo. A propósito, estou falando com você como se esse idioma pudesse lidar apenas com um blog como o meu, mas obviamente não é o caso. Facebook, Wikipedia, Yahoo, Flickr, Tumblr, todos esses sites são executados em PHP e recebem milhões de usuários todos os meses sem vacilar. O PHP faz isso muito bem e faz isso há muito tempo.

 

O futuro é agora meu velho

Você ouve há 10 anos que o PHP vai morrer. No entanto, ele ainda está aqui. Apesar do tempo e da mais recente tecnologia hipotética, ela não está se movendo. Os desenvolvedores odeiam o PHP porque é o oposto do desenvolvimento direcionado ao hype. Em uma profissão em que todos investem rapidamente nas coisas mais recentes, o PHP é um homem velho que não quer vender sua terra. Um velho que continua a dançar insolentemente.

Minha discussão com James continua normalmente e, de repente, ele me diz algo completamente chocante. “Além disso, o PHP é de longe a linguagem mais lenta que você pode encontrar para a web”. Quando ele disse que eu entendi que James não lia nada em PHP desde muito tempo.

 

PHP evolui, não sua reputação

Na mente de muitos desenvolvedores, o PHP permaneceu na versão 4. Quando a linguagem era instável, com desempenho desastroso e OOP inexistente. Os desenvolvedores odeiam o PHP porque acreditam que a linguagem está estagnada há 20 anos. Quando você conhece o PHP hoje, sabe que está longe do PHP4.

PHP7 é mais rápido que Python e Ruby. O idioma pode ser fortemente tipado, se você desejar. As ferramentas também estão evoluindo com analisadores estáticos que apareceram nos últimos anos. Estruturas como Laravel, CodeIgniter, Symfony ou Zend forçam boas práticas. Uma nova versão é lançada todos os anos com uma agenda muito precisa. Além disso, o PHP7.4 está aqui com ainda mais desempenho. Mais uma vez, não listarei tudo o que está acontecendo no PHP moderno por uma questão de comprimento. Se você quiser saber tudo, convido você a ler este artigo. É hora de mudar sua visão sobre esse idioma que nada tem a ver com o que costumava ser.

Os desenvolvedores odeiam o PHP porque é legal odiar o PHP. Esse idioma continua a ter uma reputação muito ruim por causa disso. E sim, há um legado forte e muitos problemas. Mas há muitas coisas boas também para a maioria dos sites.

 

Epílogo

A maioria dos desenvolvedores que odeiam o PHP odeiam isso por elitismo ou ignorância. De qualquer maneira, é idiota. Você tem que escolher uma tecnologia com base no que você precisa. O PHP é altamente útil e poderoso em muitos cenários. E retirá-lo da equação apenas por causa de sua reputação não é uma boa ideia.

 

Matéria traduzida deste site. Todos os créditos para Je suis un dev