Extrair valores de uma string segundo um padrão com SQL de maneira simples

 

Há uns dias necessitei de retirar alguns valores de configuração a partir de uma tabela, onde estavam codificadas com este estilo

'( [B2] = 1 ) and ( [B32] = 1 ) and ( [A38] = 1 )'

Neste caso, precisava de encontrar o “número da A”, neste caso 38.

Como sou preguiçoso e sei que daqui a uns dias iria precisar de saber também os “números da B” resolvi criar uma função que me resolvesse o problema e nunca mais tivesse de pensar no caso.

Como também gosto muito de DRY procurei nos locais habituais por soluções que se adaptassem ao meu caso.
Encontrei várias soluções interessantes, entre elas algumas com recurso a CTE’s recursivas (trocadilho intencional…) mas nenhuma resolvia o meu caso concreto.

A questão está bem resolvida quando queremos encontrar as posições do padrão na string, ou de retirar o padrão da string, mas o que necessitava era de retirar o que estava depois do ‘[A’ até ao ‘]’.

 

Resolvi partilhar o meu SQL apenas para alguém que tenha um problema similar possa pegar nisto e adaptar como entender.

Para resolver a minha situação, primeiro pensei em usar PATINDEX porque, em primeira análise, procurava um “padrão”, mas a CHARINDEX ajuda-me mais nesta situação, primeiro porque posso procurar por ‘[A’ , ‘[B’ e por ‘]’ directamente (sem ter questões com o ‘[‘ que faz parte dos caracteres que são tratados pelo LIKE como numa REGEX) e, segundo, neste caso a razão mais importante, posso usar o opcional terceiro parâmetro para procurar apenas a partir de certa posição (inexistente no PATINDEX). O código ficou muito mais simples.

 

Sei sempre que a minha string será pequena e que é uma função que vai ser chamada muito poucas vezes, tipicamente uma vez por dia, e por isso não me alonguei com questões de performance.

Fica o código de uma função similar à que acabei por implementar.

USE [TEST_DATABASE]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[ExtractPatternFromString]
(
@SomeString varchar(max)
, @SearchType char(1)
)
returns @Dependencies table (Dependency int)
with schemabinding
as
begin

declare @Separator varchar(10) = ']'
declare @Pattern varchar(10)
declare @index0 int = 0
declare @index1 int = 0
declare @index2 int = 1

if @SearchType = 'A'
  begin
    set @Pattern = '[A'
  end
else
  begin
    set @Pattern = '[B'
  end

while ( @index2 > 0)
  begin

    select @index1 = charindex(@Pattern, @SomeString, @index0) --apanhar o primeiro sítio onde está o [A
    if @index1 = 0 break; --se não existir, saltar logo do ciclo

    select @index2 = charindex(@Separador, @SomeString, @index1 )

    set @index0 = @index2

    insert into @Dependencies
      select substring(@SomeString, @index1 +2, @index2 -@index1 -2)
  end

return
end
GO

 

Para a usar basta

select Dependency from ExtractPatternFromString ( '( [B2] = 1 ) and ( [B32] = 1 ) and ( [A38] = 1 )', 'A')

Espero que possa dar alguma ajuda para resolver casos similares.

Mas que ??????? é esta? Usar variáveis e parâmetros em SSIS com menos confusão.

 

If you prefer reading this in English, the same content is available at my company’s blog.

 

Como parte do meu trabalho, uso frequentemente o SSIS, quer para desenvolver um package de raíz quer para alterar um existente.

Uma das coisas que uso e abuso são parâmetros e variáveis. Frequentemente abro uma Execute SQL Task e vejo este “lindo” código…

insert into dbo.sometable
  (col1, col2, col3, col4, col5, col6, col7)
values 
  ( ?,?,?,?,?,?,?)

Até parece que não vai haver problema nenhum… se algum parâmetro for trocado de ordem, é “simples” trocar a ordem das colunas no insert. Raramente temos de mexer na ordem dos parâmetros no mapeamento.

A questão é que, não poucas vezes, temos mais parâmetros do que este exemplo. Bastante mais… Imaginando que algumas necessitam de um cast() ou de um convert(), está instalada a confusão.

Eu uso bastante Event Handlers pela flexibilidade que têm, e muitas vezes ao criar uma mensagem de log tenho mais de 20 parâmetros que, mais tarde ou mais cedo, alguém vai pedir “uma pequena alteração”, quer seja alterar uma ordem, colocar mais um parâmetro lá pelo meio, etc.

E ao fim de algum tempo lá aparecem os erros…
[…]
SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED
[…]
Conversion failed when converting date and/or time from character string.
[…]
Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
[…]
“No value given for one or more required parameters.”
[…]
“Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
[…]
“Value does not fall within the expected range”
[…]

Para ajudar nesta situação, declaro variáveis no meu SQL para as usar com mais controlo, com o bónus de as poder reutilizar mais abaixo (raro, mas já me aconteceu)

declare @MyVariableName0 as varchar(50) = ?
declare @MyVariableName1 as varchar(10) = ?
declare @MyVariableName2 as varchar(20) = ?
declare @MyVariableName3 as varchar(50) = ?
declare @MyVariableName4 as varchar(50) = ?

insert into dbo.sometable (somemessage)
  values
  (
    ' MyVar0 = '+ @MyVariableName0 +
    ' MyVar3 = '+ @MyVariableName3 +
    ' MyVar1 = '+ @MyVariableName1 +
    ' MyVar2 = '+ @MyVariableName2 +
    ' MyVar4 = '+ @MyVariableName4 +
    ' And again MyVar0 = ' + @MyVariableName0
  )

Trocar a ordem de um parâmetro? Limpinho.
Adicionar um no meio da string? Ok, sem problemas.

Espero que vos dê tanto jeito como me dá a mim.

Dia Europeu da Internet Segura 2018

Hoje foi mais um dia europeu da internet segura.

A Microsoft e a GNR realizaram um evento nos cinemas NOS do CascaiShopping com mais de 1000 participantes, entre jovens, professores, encarregados de educação e séniores.

Juntei-me ao André Melancia em 4 workshops direccionados principalmente para  alunos com idades compreendidas entre os 10 e os 15 anos. Foi um desafio interessante por várias razões, começando logo pela necessidade de explicar “por miúdos” algumas coisas que muitos adultos ainda têm dificuldade em perceber… pensávamos nós.

Lá fomos apresentando a nossa sessão de “hacking levezinho”…

  • entrar num servidor de email e enviar uma mensagem que parecia partir de uma conta à qual não tínhamos acesso e que o “atacado” conhecia
  • esse mail tinha incluido um link para o que parecia um vídeo do youtube… que acabava por aparecer apenas depois de muita coisa ter acontecido pelo “meio”
  • mostrar que uma imagem partilhada numa rede social, mesmo depois de “apagada” se mantinha disponível
  • mostrar o que pode acontecer ao colocarmos no nosso computador um qualquer dispositivo USB que encontramos no chão
  • o que pode acontecer quando clicamos numa “actualização” que de repente nos aparece a meio da nossa navegação
  • ensinar a diferença entre uma ligação segura e não segura
  • mostrar a importância de uma boa password

Tivémos muitas surpresas durante as sessões: muitas perguntas pertinentes, muitos miúdos com bastante conhecimento sobre o que fazer em situações semelhantes, muitos exemplos dados por eles de como “aquilo” já lhes tinha acontecido e como eles resolveram.

A surpresa do dia? Vimos mais professores e encarregados de educação assustados com o que viram do que a maioria dos pequenotes. Quando mostrei o dispositivo USB que permitia a introdução de software num computador cheguei a receber um “não acredito” vindo de um professor.

Uma manhã bem passada que, sem dúvida, é para repetir.

Para o ano há mais… e já tem data: 5 de fevereiro.

Connect() 2017, novidades de Data Platform

As novidades de Data Platform condensadas em uma horinha e picos…

10 minutos de apresentação pelo Rohan Kumar com a usual conversa mais comercial. A reter que foram mais de 2 milhões de downloads de versões beta do SQL 2017 (10x mais do que as do SQL 2016).

Depois são 50 minutos sempre a abrir com muita informação e algumas demos interessantes.

Fala-se de novas funcionalidades, como Graph Data, Machine Learning com R e Python directamente na BD, Adaptive Query Processing e Automatic Plan Correction.

Uma demo gira de uma empresa que mudou de Redshift para SQL 2016 e, aquando da saída do SQL 2017, aproveitando o know-how que tinham, mudaram para Docker + Linux o seu ambiente de produção.

Deu-se alguma importância ao Azure Database Migration Service.
Realmente um dos obstáculos à adoção de Azure SQL Database é o inerente custo da alteração do código. Fala-se em “maioria das aplicações” a migrar sem problemas (cerca de 53%, segundo o Rohan), valor que com Managed Instances querem que chegue aos 100%. Uma boa promessa para quem precisa de CLR ou SQL Agent.
Boas notícias para quem tem Software Assurance, pois com o Azure Hybrid Benefit há grandes vantagens ao nível do preço. Ainda não há valores, mas para Windows Server chega aos 82% de desconto.

Como fazer performance tuning automático ou sermos avisados se a nossa BD está a ser atacada? “Simples”. Analisando 700TB de telemetria por dia dos 2 milhões de BD activas e usando modelos de Machine Learning em cima disso. Eu disse que era simples?

Mais à frente há uma demo interessante com o novo PREDICT que, mantendo o modelo de Machine Learning dentro da BD, dispensa de retirar de lá os dados, melhorando a performance para valores “interessantes”.

Mais demos, uma de PostgreSQL migrada para Azure e outra de CosmosDB, rematada com informação sobre o novo serviço Databricks e… o tempo chegou ao fim. 🙂

 

Connect() 2017, primeiras impressões

 

Começou ontem mais um Microsoft Connect(), um evento virado para a área do desenvolvimento (mas não só).

A keynote principal de ontem apresentou bastantes novidades, como já vem sendo hábito neste tipo de sessões. Cheguei a ter de parar o vídeo por várias vezes para conseguir assimilar algumas das coisas que estava a ver, principalmente quando o Scott Hanselman estava em palco.

Para quem tem andado desatento, o início foi “estranho”. Uns minutos de Scott Guthrie e depois uma demo em que aparece um senhor que começou numa janela de Visual Studio Code a fazer uma app Node… num Mac. Usa o Google Chrome como browser, fala pelo Slack com uma colega, envia-lhe um link e ela abre o Visual Studio no Windows dela mas… o código é o que está no Visual Studio Code do colega. Pair Programming, incluindo uma sessão de debug onde num breakpoint ela tem acesso a tudo o que está a acontecer… na máquina do colega… que, como já disse, é um Mac. A correr MacOS. Depois disso, faz deploy num App Service em Azure do container Docker em Linux.

Ainda vamos nos primeiros vinte minutos da hora e meia de keynote.

Mais informações sobre este Visual Studio Live Share aqui e o pedido para aceder ao preview está aqui.

 

Destaco também a apresentação do Scott Hanselman pelo minuto 50. Para quem está dentro de Microservices e Kubernetes é imperdível.

 

Muitas mais novidades foram apresentadas. Os meus destaque vão para:

A possibilidade de criarmos bibliotecas em C# para usarmos nativamente em aplicações iOS e Android.

A apresentação do Visual Studio App Center (antigo Mobile Center) que saiu de preview.

A fantástica demo do Donovan Brown que mostrou em directo o Visual Studio Team Services aberto na conta da equipa que… faz o Visual Studio Team Services. Não é todos os dias que podemos ver informação real de uma equipa de 500 engenheiros, mas ainda não foi desta que pudemos ver a conta dos 25000 engenheiros da equipa do Windows…

Uma boa demo da Lara Rubbelke a arrancar uma Azure SQL Database em menos de um minuto e a mostrar uma migração de uma BD on premises para Azure enquanto o diabo esfrega um olho…

O novo SQL Operations Studio, um IDE gratuito e multiplataforma baseado no Visual Studio Code que vem ao encontro do muito pedido Management Studio para macOS e Linux. Assim que experimetar no meu Mac tento fazer um outro post…

A entrada da Microsoft na MariaDB foundation e a adicão deste SGBD aos fully managed MySQL e PostgreSQL.

Para quem anda nas lides do Big Data, de referir que agora podem usar as APIs com CosmosDB.

Mantendo o tema, a demo da Databricks sobre o serviço homónimo de Apache Spark está supimpa!

 

E, por agora, é tudo. Tantos vídeos para ver e tão pouco tempo… 🙂

 

 

Quando se escreve pouco…

Quando se escreve pouco… é porque está muito a acontecer ao mesmo tempo. 🙂

 

Em termos de eventos, desde a 5ª reunião da Cloud Pro PT em que falei sobre as novidades do Visual Studio Mobile Center, que houve novidades.

Estive no 13º encontro da comunidade IT Pro Portugal a fazer um Bot em 30 minutos. Quem por lá apareceu viu que os enganei bem, pois fazer o Bot não demorou nem pouco mais ou menos esse tempo. Para falar verdade, fiz o Bot, treinei-o, publiquei-o, inseri-o numa página de Facebook e num site em menos de 20 minutos, mesmo falando em inglês porque tínhamos lá convidados estrangeiros. Está na calha um vídeo sobre este processo que irei publicar mais tarde aqui.

 

A maior novidade aconteceu neste fim-de-semana. Com mais dois grandes malucos, o André Melancia e o Ricardo Cabral, contado também com o apoio de muitos profissionais de Data Platform, foi criada a Data Community Portugal. O primeiro evento foi realizado no sábado, nas instalações da Microsoft Portugal no Parque das Nações.

O formato escolhido funcionou muito bem. Um workshop “entry-level” sobre instalação de SQL Server. E para um evento anunciado na terça-feira anterior a ser feito num sábado de verão com tempo de praia correu muito bem, com uma sala cheia de interessados. Realmente, a repetir.

Cheguei mesmo a instalar SQL Server no meu macOS. Espero ter tempo para fazer um post sobre a experiência. Foi “limpinho”… usando a imagem Docker e tendo apenas cuidado com algumas dependências.

Agora… de volta ao código. 🙂

 

 

 

Mais Visual Studio Mobile Center, amanhã na 5ª reunião da Cloud Pro PT

É já amanhã!

Apareçam pela Microsoft pelas 18h30 para um pouco de comida e boa conversa, depois pelas 19h o Marco Cerruti vai falar sobre Azure Active Directory e depois do intervalo falo eu sobre Visual Studio Mobile Center.

Aproveitem bem o evento, se puderem fiquem connosco para um jantar “por ali” depois do evento 🙂

É grátis mas sujeito a inscrição pelo Eventbrite aqui.

 

E se, de repente, um desconhecido lhe oferecer flores…

Creio que muitos dos que lêm este blog se lembram desta icónica frase. Anos 80, anúncio a um desodorizante em spray.

“E se, de repente, um desconhecido lhe oferecer flores… isso é Impulse”.

Há muitas frases que vão ficando na nossa memória.  Outra delas aprendi-a com a minha mulher.  “Quando é bom demais para ser verdade, é porque é bom demais para ser verdade”.

Falta mais uma parte para completar a “trilogia” que me fez escrever este post e fazer-vos perceber onde quero chegar.

Há alguns anos (achava eu que foram 3 ou 4, mas vendo bem acho que foram mais…) chegava à empresa onde trabalhava na altura e fui abordado por uma colega, a “Isabel” (nome fictício, claro) que me “dispara” esta pérola:

Ela: “Olha, apareceu-me aqui no computador uma mensagem”

Eu: “E que mensagem era?”

Ela: “Não sei, não vi bem…”

Eu: “E tu, o que fizeste?”

Ela, percebendo pela minha cara que já esperava a resposta que ela ia dar: “Carreguei… no SIM…”

Para quem me conhece bem, há alguns anos era totalmente natural “passar-me dos carretos” com coisas destas. Aconteceu muitas vezes. Daquela vez mudei de estratégia e inventei uma situação para tentar explicar melhor a mensagem que queria passar.

Eu: “Imagina que um desconhecido te aborda na rua e te pede €20. O que fazes?”

Ela: “Não dou”

Eu: “Claro, porque não o conheces de lado algum… até te digo mais, tu só dás os €20 se conheceres a pessoa e se tiveres confiança nela. E se tiveres €20 na carteira.”

Terminei a conversa com estas frases: “Quando as pessoas perceberem que temos de ser com os computadores como somos no resto da vida, tudo será mais simples. Aparece uma mensagem… lê o que lá está. Por omissão respondes NÂO. Só respondes sim SE. SE perceberes o que te está a ser pedido. SE perceberes de onde veio aquela mensagem. SE conheceres a aplicação que te está a pedir aquilo. SE conseguires perguntar a alguém que está mais por dentro do assunto e SE ele te disser que podes responder sim. Mas em todos os outros casos, NÃO. Aqui ou no resto da tua vida, só respondes sim “SE”. Até as pessoas perceberem isso e deixarem de dizer que não são informáticas para saberem essas coisas vão continuar a existir estes problemas”.

Lembrei-me disto por causa de um dos assuntos do momento, para além de Fátima, Futebol e Festival da Eurovisão.  Ransomware. De repente computadores com mensagens a dizer que os ficheiros estão encriptados e se não pagarem um X vão apagar tudo do seu computador.

Esta vaga de infeccções deveu-se, em parte, a uma falha de segurança num componente do Windows, presente apenas em versões antigas e em que até já havia um patch de segurança que resolvia a questão mas que não tinha sido aplicado nas máquinas que ficaram infectadas. Isso é um outro assunto que provavelmente quero abordar em outro post.

Mas não foi essa falha que fez com que a infecção começasse. Provavelmente foi um anexo de email que alguém resolveu abrir, porque… porque sim. Quem não recebeu um mail de um “amigo” que não conhecemos de lado algum com as “fotos do fim-de-semana passado”, aquele que passámos em casa com a família? Que não recebeu um mail do banco a dizer que tem uma transacção para aprovar sem ter ido ao banco nem ter feito uma transacção? Quem não tem uma encomenda da UPS ou da DHL à sua espera em qualquer lado mas não encomendou nada? Quem não recebeu um mail da Google a pedir para confirmar as suas credenciais  respondendo com utilizador e password? Já nem imagino as que ainda acreditam que há alguém na Nigéria que desesperadamente necessita de um sócio para retirar dinheiro de lá a troco de 10% do valor mas precisa primeiro de algum dinheiro para inciar o processo. E claro, está lá um link para qualquer lado que não sabemos bem ou pior ainda, um anexo qualquer que resolvemos abrir “para ver o que é”. Claro que se um desconhecido de repente lhe pedir para ir para um beco para ver uma coisa qualquer… todos iríamos, não era?

Há muitos anos dei um raspanete a um colega porque ele resolveu abrir um anexo que vinha não-sei-de-quem com o texto não-sei-quê e com um anexo não-faço-ideia-nenhuma. Automaticamente o computador ficou infectado e teve de ser reinstalado. Felizmente a primeira coisa que ele fez foi ligar-me mas esse minutinho em que o pc esteve na rede, em termos informáticos, é uma eternidade.

Sejemos com os nossos computadores como somos com o resto. “NÂO” é a resposta por omissão. E, já agora, quando vêm uma actualização do sistema operativo, façam-na, pela saúde do vosso computador.

Se, de repente, um desconhecido lhe oferecer qualquer coisa…

Build 2017, parte 2

Aqui fica a segunda parte das minhas escolhas da Build 2017. Não tem sido fácil acompanhar isto tudo, ainda por cima em semana de TugaIT… por falar nisso, já se inscreveram? Não sei se ainda há lugares disponíveis, mas aproveitem enquanto é tempo.

 

Ainda das pré-gravadas, há muita informação interessante.

Build bots in Skype for Business, https://channel9.msdn.com/Events/Build/2017/P4135

Agora podemos também fazer Bots com a Bot Framework para Skype for Business, para além dos já existentes como o Skype, Slack, Microsoft Teams, Twillio, Telegram, Facebook Messenger e mais alguns.

 

Bot Human Handoff, https://channel9.msdn.com/Events/Build/2017/P4075

Como iniciar uma conversa com um Bot e depois fazer a transição para uma pessoa. Uma parte inicial mais “chata” mas importante para percebermos o que acontece depois durante a demo.

 

Creating FAQ bots with QnA Maker, https://channel9.msdn.com/Events/Build/2017/P4074

Criar com o QnA Maker para transformar um FAQ já existente num Bot. Uma excelente demo para uma funcionalidade muito interessante.

 

Dialog management in Bot Framework, https://channel9.msdn.com/Events/Build/2017/P4070

De novo o @GeekTrainer a falar sobre Bots, desta vez como usar o stack de diálogos para uma melhor experiência de conversa.

 

Designing conversational UI for bots (and humans), https://channel9.msdn.com/Events/Build/2017/P4066

Como criar um Bot na perspectiva do design e interação com o utilizador.

 

How your applications can benefit from AI using Bing APIs on Microsoft Cognitive Services, https://channel9.msdn.com/Events/Build/2017/P4060

Explorar um pouco as API’s disponíveis para integração nas nossas aplicações, desde o Search até Speech.

 

Agora entrando já nas gravadas durante o evento…

What’s new with the Microsoft Bot Framework, https://channel9.msdn.com/Events/Build/2017/B8097

Sessão sobre as novidades da mais recente Bot Framework. Um pouco “por cima” de início mas com uma boa demo depois.

 

Bot capabilities, patterns and principles, https://channel9.msdn.com/Events/Build/2017/B8010

Com a presença do nosso conhecido Mat Velloso (participou num evento aqui na Microsoft em Lisboa há uns anos e, como bom brasileiro, é bastante simpático e afável) fala-se de Bots, Bots, Bots e mais Bots. Já disse que se fala de Bots? Não deixem de ver a parte das perguntas e respostas.

 

 

Agora “basta” arranjar tempo para ver as dezenas que já escolhi para ver (e não estou a brincar… já passa dos 100 vídeos.)

Primeiras escolhas da conferência Build 2017

A Build, para quem não sabe, é a principal conferência técnica da Microsoft.

Há muitas novidades interessantes que já devem ter visto em vários blogues e revistas da especialidade, por isso não me vou focar muito nelas, pelo menos para já, esperando fazê-lo quando “meter a mão na massa”.

O conteúdo disponível é enorme, como sempre. Há sempre que escolher entre as centenas de vídeos que vão sendo colocados no Channel9.

Deixo-vos aqui uma pequena seleccção do que já vi e que recomendo, baseado nos meus interesses actuais.

 

Aplicações móveis

The next generation of HockeyApp: Visual Studio Mobile Center, https://channel9.msdn.com/Events/Build/2017/P4069

Integração das funcionalidades da HockeyApp no novo Mobile Center.

 

Advanced cross-platform mobile apps with Azure Mobile Apps and Xamarin, https://channel9.msdn.com/Events/Build/2017/P4121

O James Montemagno com uma pequena demo de uma app com serviços Azure AppService.

 

Designing mobile applications, https://channel9.msdn.com/Events/Build/2017/P4048

Mais virada para o design da aplicação, com muito boas dicas de UI.

 

Criar Apps para Microsoft Teams

How to package a Microsoft Teams app, https://channel9.msdn.com/Events/Build/2017/P4150

Primeiros passos para criar um package para upload nas Teams.

 

Build a great tab for your Microsoft Teams app, https://channel9.msdn.com/Events/Build/2017/P4157

Um pequeno vídeo com os passos para criar um novo separador nas Teams.

 

Notify your users through your Microsoft Teams app, https://channel9.msdn.com/Events/Build/2017/P4132

Colocar mensagens na Activity Feed, Bots e Connectors

 

Build a great bot for Microsoft Teams, https://channel9.msdn.com/Events/Build/2017/P4126

Todos os passos para criar um Bot com a Bot Framework

 

Compose extensions for Microsoft Teams, https://channel9.msdn.com/Events/Build/2017/P4151

Depois de termos o Bot feito, como criar uma extensão em menos de 90 linhas de código.

 

Vou tentando colocar aqui mais algumas das sessões que considero relevantes.