SQL vs Mongo - expressões regulares

Veja como utilizar expressões regulares no SQL e no Mongo

As expressões regulares são padrões de texto que permitem extrair ou substituir informações. Podemos usar as expressões regulares tanto na programação quanto nos bancos de dados.

Vamos ver exemplos de como as expressões regulares podem nos ajudar a selecionar informações em nosso banco de dados, faremos comparações utilizando SQL e Mongo

Observação:A implementação das expressões regulares não é igual em todos os bancos de dados.

Este é o terceiro artigo comparando operações no SQL com seus equivalentes no Mongo. Perdeu os artigos anteriores? Confira os links abaixo:

SQL vs Mongo - Operações básicas

SQL vs Mongo - consultas com agrupamento

Para testar os recursos demonstrados neste artigo, será necessário criar uma tabela de testes no SQL, e na sequência, criar a nossa coleção de testes no Mongo.  Começando pelo SQL:

//SQL SERVER

CREATE TABLE alunos (

ID INT IDENTITY (1,1) PRIMARY KEY,

nome varchar(100),

cep varchar(20)

)

//MySQL

CREATE TABLE alunos (

ID int(11) NOT NULL AUTO_INCREMENT,

nome varchar(100) COLLATE latin1_general_ci DEFAULT NULL,

cep varchar(20) COLLATE latin1_general_ci DEFAULT NULL,

PRIMARY KEY (ID) 

ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Criada a tabela, chegou a hora de carregar os dados para testes.

INSERT INTO alunos (nome, cep) VALUES ('Silva Matheus','01532-001')

INSERT INTO alunos (nome, cep) VALUES ('Costa Francisco','01533001')

INSERT INTO alunos (nome, cep) VALUES ('Matos Yolanda','CEP01532')

INSERT INTO alunos (nome, cep) VALUES ('João Barros', '01532-0001')

INSERT INTO alunos (nome, cep) VALUES ('João 3', '01532-0001')

INSERT INTO alunos (nome, cep) VALUES ('Maria Santos','01534001')

INSERT INTO alunos (nome, cep) VALUES ('João!','01534001')

INSERT INTO alunos (nome, cep) VALUES ('Maria','01534001')

INSERT INTO alunos (nome, cep) VALUES ('Yolanda Costa','01535001')

INSERT INTO alunos (nome, cep) VALUES ('Francisco Francisco','01533001')

Agora vamos criar a nossa coleção no Mongo. Lembrando que para criar uma coleção no Mongo não precisamos definir antes a estrutura dos dados. Neste exemplo, utilizando programação fazemos um loop num array para criar registros aleatórios. 

use meu_banco_de_dados

(function() {

  var nomes = ['Silva','Matheus','Costa','Francisco','Matos','Yolanda','Barros', 'Santos','Maria','João', 'João!', 'João 3'];

  var CEPs = ['01532-001', '01533001', 'CEP01532', '01532-0001', '01534001', '01535001'];

  var nomeAleatorio = function() {

    var n = nomes.length;

    return [

      nomes[Math.floor(Math.random() * n)],

      nomes[Math.floor(Math.random() * n)]

    ].join(' ');

  };

  var cepAleatorio = function() {

    var n = CEPs.length;

    return [

      CEPs[Math.floor(Math.random() * n)]].join('');

  };

  for (var i = 0; i < 500; ++i) {

    var aluno = {

      nome: nomeAleatorio(),

      cep: cepAleatorio()

    }

    db.alunos.insert(aluno);

  };

})();

Para validar se os dados foram inseridos, basta digitar:

//SQL

SELECT * from alunos

//Mongo

db.alunos.find();

Com base neste dados, vamos ver as seguinte operações:

 

 

  • Selecionar registros de acordo com o tamanho

 

 

  • Localizar um determinado valor em algum lugar do campo

 

 

  • Selecionar registros que estejam dentro de um determinado formato

 

 

  • Limpeza de dados

 

 

 

Selecionar registros de acordo com o tamanho

Vamos selecionar os registros que possuem o campo nome com 12 caracteres. Para fazer isso no SQL, podemos fazer da seguinte forma:

//SQL SERVER

SELECT * FROM alunos WHERE LEN(nome) = 12

//MySQL

SELECT * FROM alunos WHERE LENGTH(nome) = 12

Agora vamos ver o mesmo utilizando expressões regulares no Mongo

db.alunos.find({"nome": /^.{12}$/})

E se for necessário selecionar os nomes que tenham o tamanho maior ou igual a 18 caracteres?

//SQL SERVER

SELECT * FROM alunos WHERE LEN(nome) >= 18

//MySQL

SELECT * FROM alunos WHERE LENGTH(nome) >= 18

//Mongo

db.alunos.find({"nome": /^.{18,}$/})

E para selecionar os nomes que tenham até 15 caracteres podemos fazer da seguinte forma:

//SQL SERVER

SELECT * FROM alunos WHERE LEN(nome) <= 15

//MySQL

SELECT * FROM alunos WHERE LENGTH(nome) <= 15

//Mongo

db.alunos.find({"nome": /^.{0,15}$/})

Operadores que foram utilizados nestes exemplos:

 

 

  • / / Define a nossa expressão regular. A regra que vai ser aplicada fica entre as barras.

 

 

  • ˆ Indica uma pesquisa somente no inicio do bloco.

 

 

  • $ Indica uma pesquisa somente no fim do bloco.

 

 

  • [ ] Define uma lista de caracteres. 

 

 

  • {x} Repete exatamente "x" vezes um caractere ou uma lista.

 

 

  • {x,y} Repetir no minimo "x" ou no máximo "y" um caractere ou uma lista.

 

 

  • . Indica qualquer caractere. 

 

 

 

Localizar um determinado valor em algum lugar do campo

Vamos selecionar todos os registros que tenham o nome "Yolanda", em qualquer posição deste campo.

//SQL

SELECT * from alunos where nome like '%Yolanda%'

//Mongo

db.alunos.find({nome: /Yolanda/})

Se eu desejar selecionar todos os registros que não possuem este nome, podemos fazer da seguinte forma

//SQL

SELECT * from alunos where nome NOT like '%Yolanda%'

//Mongo

db.alunos.find({nome: /^((?!Yolanda).)*$/})

Podemos ajustar a nossa pesquisa por nome para selecionar os registros que tenham Yolanda no começo ou no fim do nome. Para selecionar apenas registros que tenham este nome no começo, podemos fazer da seguinte forma:

//SQL

SELECT * from alunos where nome like 'Yolanda%'

//Mongo

db.alunos.find({nome: /^Yolanda/})

E para selecionar registros que tenham este nome no final podemos fazer assim:

//SQL

SELECT * from alunos where nome like '%Yolanda'

//Mongo

db.alunos.find({nome: /Yolanda$/})

Operadores que foram utilizados nestes exemplos:

 

 

  • / / Define a nossa expressão regular. A regra que vai ser aplicada fica entre as barras.

 

 

  • ˆ Indica uma pesquisa somente no inicio do bloco.

 

 

  • $ Indica uma pesquisa somente no fim do bloco.

 

 

  • ?! - Não deve conter a regra informada

 

 

 

Selecionar registros que estejam dentro de um determinado formato

Vamos selecionar os nomes que tenham sido informados de forma correta. Por exemplo, eu não posso ter um nome gravado como "Joao 3". Também não posso ter um nome "Joao!". Mas, também não posso ter um nome apenas como "Joao".  Os nomes dos alunos devem conter somente letras e espaços, ou seja, eu devo ter nome e sobrenome, sem números ou caracteres especiais.

Uma expressão que pode selecionar os nomes corretos é esta:

([A-Za-z])+( [A-Za-z]+)

Como é necessário selecionar os nomes que NÃO são compatíveis com a expressão, no caso do Mongo, precisamos fazer a seleção inversa. A expressão regular ficaria da seguinte forma:

/^(?!([A-Za-z])+( [A-Za-z]+)*$)/

Perceba que para inverter a expressão regular colocamos a nossa expressão dentro de "(?!)"

//SQL Server

SELECT * FROM alunos WHERE  CHARINDEX(' ', nome) = 0 or (nome not LIKE '[A-Z][a-z]%[A-Z][a-z]')

//MySQL

SELECT * FROM alunos WHERE nome NOT REGEXP '([A-Za-z])+( [A-Za-z]+)'

//Mongo

db.alunos.find({nome: /^(?!([A-Za-z])+( [A-Za-z]+)*$)/})

 

Operadores que foram utilizados nestes exemplos:

 

  • / / Define a nossa expressão regular. A regra que vai ser aplicada fica entre as barras.
  • ( ) Define um bloco de caracteres.
  • + Repete pelo menos uma vez.
  • w  Seleciona todos os caracteres alfanuméricos, ou seja, letras e números, mas não acentos ou caracteres especiais. É o equivalente a [a-zA-Z_0-9]
  • s  Seleciona espaços.

 

Limpeza de dados

Agora vamos padronizar o campo CEP. Em nossa carga de dados, inserimos CEP com traço, sem traço, com texto e com quantidade de números errada. Vamos primeiro padronizar os registros, removendo traços ou pontos, para depois validar se o tamanho está correto. O CEP possui 8 dígitos, sendo que existe um separador (-) após o quinto número. 

Para deixar apenas números, vamos fazer o seguinte UPDATE:

//SQL SERVER

UPDATE alunos SET cep = REPLACE(TRANSLATE(cep, 'abcdefghijklmnopqrstuvwxyz+()- ,#+', '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@', '')

//MySQL a partir da versão 8

update alunos set cep = REGEXP_REPLACE(cep, '[^0-9]+', "")

No caso do Mongo, será necessário fazer um loop nos documentos da coleção para poder remover o conteúdo não númérico do campo CEP.

//Mongo

db.alunos.find().forEach(function(doc) {

  doc.cep = doc.cep.replace(/[^0-9]/g, ''); 

  db.alunos.save(doc);

});

Agora que fizemos a padronização de dados será possível fazer a pesquisa.  Uma das expressãoes regulares que vamos utilizar para selecionar os registros corretos é a seguinte:

/^d{8}$/

Se tivesemos mantido o traço, ela poderia ser da seguinte forma:

/^d{5}-d{3}$/

Como queremos apenas os registros errados, no caso do Mongo, vamos inverter a expressão regular:

/^(?!d{8}$)/

Agora fazendo a nossa pesquisa:

//SQL Server

SELECT * FROM alunos WHERE cep NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' 

//MySQL

SELECT * FROM alunos WHERE cep NOT REGEXP '^[0-9]{8}$';

//Mongo

db.alunos.find({cep: /^(?!d{8}$)/})

Operadores que foram utilizados nestes exemplos:

 

 

  • / / Define a nossa expressão regular. A regra que vai ser aplicada fica entre as barras.

 

 

  • ˆ Indica uma pesquisa somente no inicio do bloco.

 

 

  • $ Indica uma pesquisa somente no fim do bloco.

 

 

  • [ ] Define uma lista de caracteres. 

 

 

  • {x} Repete exatamente "x" vezes um caractere ou uma lista.

 

 

  • ? Declara um ou mais caracteres como opcionais.

 

 

  • + Repete pelo menos uma vez.

 

 

  • . Indica qualquer caractere. 

 

 

  • d  Equivale a [0-9], seleciona apenas números.

 

 

 

 

Quer conferir mais dicas sobre banco de dados? Clique aqui e veja mais dicas!