quarta-feira, 14 de novembro de 2012

Sql Server operador LIKE

Eventualmente precisamos recuperar registros do banco de dados de acordo com um determinado padrão de string. O Sql Server disponibiliza duas formas de efetuar esse filtro, através do operador LIKE ou do predicado CONTAINS. Neste post vamos abordar o funcionamento do operador LIKE.

De uma forma geral utilizamos o operador LIKE na clausula WHERE da seguinte forma:

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

Onde:


  • match_expression é uma expressão sql válida que retorna um tipo string (CHAR, VARCHAR, NVARCHAR, NCHAR);
  • [NOT] operador opcional utilizado para negar qualquer expressão boleana;
  • LIKE operador responsável pelo filtro, retorna TRUE se for encontrado o padrão pattern em match_expression, caso contrario retorna FALSE;
  • pattern é o padrão que será utilizado para efetuar o filtro nos registros com base na match_expression, pode ter até 8000 caracteres;
  • [ ESCAPE escape_character ] carácter opcional usado como literal para flexibilizar o operador LIKE informando que o carácter coringa deve ser interpretado como um carácter regular não como carácter curinga, veremos alguns exemplos desses caracteres abaixo. 
Para executar os exemplos utilizaremos o Sql Server Management Studio do Microsoft SQL Server 2008 (SP3) - 10.0.5512.0

Num banco de dados qualquer criado por você, abra uma nova consulta com conexão local (arquivo > novo > consulta com conexão local). Vamos criar uma tabela temporária e inserir alguns registros para fazer os testes.

Script da tabela temporária:


CREATE TABLE #CONTATO
(
 ID INT,
 NOME VARCHAR(100),
 EMAIL VARCHAR(100),
 PERCENTUAL VARCHAR(4)
)

Script para inserir os registros:


INSERT INTO #CONTATO VALUES
(1,'Maria de Fatima','mariaf@gmail.com','5%'),
(2,'Antonio Jose','antoniojose@yahoo.com','15%'),
(3,'Fernanda de Carvalho','fc@yahoo.com','70%'),
(4,'Uelinton Josenildo','josenildo.u@gmail.com','35%'),
(5,'Welinton Barbosa','wb@yahoo.com','84%')

Obs: Não feche a sua conexão, está é uma tabela temporária e está em memória, se a conexão for interrompida ela deixará de existir, outra forma de eliminar a tabela é através do comando DROP TABLE #CONTATO)

Vamos aos exemplos:

1- Selecionar todos os registros que no campo nome esteja contido exatamente o termo "Maria de Fatima".

SELECT * FROM #CONTATO WHERE nome like 'Maria de Fatima'

No comando acima estamos dizendo para o banco "eu quero todos os registros que no campo nome contenha exatamente a string "Maria de Fatima". Neste caso teríamos como resultado o registro cujo id é igual a 1.

2- Vamos supor que nossa aplicação executa um procedimento de pesquisa no banco onde a string de pesquisa não contenha espaços em branco, todo espaço em branco é substituído por underline, por exemplo "Antonio_Jose". Então para pesquisar todos os registros que no campo nome esteja contido exatamente o termo "Antonio_Jose" precisaremos fazer uma operação na coluna nome ficando da seguinte forma:

SELECT * FROM #CONTATO where REPLACE(nome,' ','_') LIKE 'Antonio_Jose'

Neste caso o operador LIKE está validando o resultado da expressão (REPLACE(nome,' ','_')) que contem a coluna nome, em vez de validar o termo diretamente na coluna como no exemplo 1. Neste caso teríamos como resultado o registro cujo id é igual a 2.


3- Agora vamos começar a flexibilizar o operador LIKE. Queremos selecionar todos os registros cuja a coluna nome contenha, em qualquer lugar, a string "Jose". Neste caso devemos utilizar o carácter curinga %.

SELECT * FROM #CONTATO where nome LIKE '%Jose%'

No comando acima estamos dizendo, "eu quero todos os registros que contenha o termo 'Jose' em qualquer parte da string presente na coluna nome". Desta forma seriam retornados os registros 2 (Antonio Jose) e 4 (Uelinton Josenildo). Se quiséssemos apenas os registros cujo o campo nome começassem com a string 'Jose' o carácter curinga só seria colocado a frente do termo, formando o pattern 'Jose%'. Como não existe nenhum nome que comece com Jose, nenhum registro será retornado.


4- E se o campo a ser pesquisado contivesse um carácter curinga  Neste caso precisamos de um artifício que faça o Sql Server interpretar o carácter curinga como um carácter literal. Para resolver o problema utilizamos a palavra reservada ESCAPE e um carácter auxiliar como no exemplo abaixo:

SELECT * FROM #CONTATO WHERE PERCENTUAL LIKE '%@%%' ESCAPE '@'

O select acima retornará todos os registros porque todos os dados da coluna percentual terminam com o sinal "%" que neste caso é interpretado como um carácter literal. Outra forma de obter o mesmo resultado seria colocando o carácter curinga entre colchetes. Assim:


SELECT * FROM #CONTATO WHERE PERCENTUAL LIKE '%[%]%'

5- Podemos pesquisar um único carácter também. Para obter esse recurso utilizamos o carácter underline. O select abaixo pode ser traduzido da seguinte forma: Pegue todos os contatos cujo nome comece com qualquer letra e termine com 'elinton'.

SELECT * FROM #CONTATO WHERE NOME LIKE '_elinton'

O select acima não retornará nenhum registro porque não existe nenhum contato onde o nome tenha apenas uma palavra. No exemplo 6 poderemos resolver esse problema.

6- Continuando com o mesmo problema do exemplo 5, faremos uma pequena modificação para poder obter todos os nomes que comecem com qualquer letra seguido da string 'elinton', porem desconsideraremos a string que vier depois, se houver. Neste caso devemos utilizar um segundo carácter curinga  Sim! Podemos utilizar mais de um carácter curinga. Vejamos como fica:

SELECT * FROM #CONTATO WHERE NOME LIKE '_elinton%'

Como resultado teremos os registros de id 4 e 5. Porque estamos dizendo:"eu não sei qual é a primeira letra mas depois dela vem a string 'elinton' e depois pode vir qualquer coisa".

7- No exemplo 6 resolvemos o problema da pesquisa, mas deixamos muito flexível, visto que o servidor terá que testar todos os carácteres possíveis. Podemos limitar essa pesquisa da seguinte forma:

SELECT * FROM #CONTATO WHERE NOME LIKE '[UW]elinton%'

Veja que agora estamos dizendo, "eu não sei qual é a primeira letra, mas sei que pode ser U ou W seguido de 'elinton' e o resto dos carácteres pode ser qualquer coisa.". Dessa forma limitamos o primeiro curinga a duas letras, mas poderia ser também um intervalo da seguinte forma:

SELECT * FROM #CONTATO WHERE NOME LIKE '[M-W]elinton%'

Assim o Sql Server tentará encontrar um nome cuja a primeira letra seja alguma de M até W.

8- E por fim podemos usar o carácter ^ (acento circunflexo) para excluir um carácter, conjunto ou intervalo. Vejamos abaixo como fica:

SELECT * FROM #CONTATO WHERE NOME LIKE '[^W]elinton%' 

No exemplo acima estamos dizendo "o primeiro carácter pode ser qualquer um menos o W". Neste caso somente o registro cujo id é 4 será retornado. Abaixo, vamos fazer a exclusão de um grupo de carácteres:

SELECT * FROM #CONTATO WHERE NOME LIKE '[^ABCD]elinton%'

Acima podemos ver como podemos dizer ao Sql Server que não queremos determinados carácteres. No exemplo informamos que os contatos podem começar com qualquer letra menos A,B,C,D, seguido da string 'elinton'. O exemplo acima poderia ser escrito também como um intervalo da seguinte forma:

SELECT * FROM #CONTATO WHERE NOME LIKE '[^A-D]elinton%'

Bom pessoal, espero ter conseguido exemplificar um pouco do funcionamento do operador LIKE no Sql Server. Antes de terminar gostaria de salientar algumas observações importantes:


  1. Verifique a collation do seu servidor/banco/tabela/coluna, a depender da configuração o resultado pode não ser o esperado;
  2. Atente para os tipos de dados da match_expression e pattern, CHAR e VARCHAR armazenam os dados de forma diferente;
  3. Quandos os argumentos (match_expression e pattern) são do tipo ASCII tudo será executado como ASCII, caso um dos argumentos sejam Unicode, todos serão convertidos para Unicode.


Nenhum comentário:

Postar um comentário