curso
Tutorial de correspondência de padrões SQL LIKE
Quer você goste ou não, o operador LIKE
é essencial no SQL. Ele oferece aos cientistas e engenheiros de dados a capacidade de filtrar dados com base em correspondências de strings específicas. Este artigo fornece um tutorial rápido sobre LIKE
para iniciantes e intermediários.
O operador LIKE do SQL para correspondência de padrões
Observação: Para executar facilmente todo o código de exemplo deste tutorial, você pode criar uma pasta de trabalho gratuita do DataLab com oSQL instalado e bancos de dados com dados de amostra.
Suponha que você tenha uma tabela employees
e queira encontrar todos os nomes que começam com ‘A’
:
funcionários |
|||||
emp_no |
birth_date |
first_name |
last_name |
gênero |
hire_date |
10001 |
1953-09-02T00:00:00.000Z |
Georgi |
Facello |
M |
1986-06-26T00:00:00.000Z |
10002 |
1964-06-02T00:00:00.000Z |
Bezalel |
Simmel |
F |
1985-11-21T00:00:00.000Z |
10003 |
1959-12-03T00:00:00.000Z |
Parto |
Bamford |
M |
1986-08-28T00:00:00.000Z |
Você pode gastar tempo examinando a tabela manualmente. Mas por que você faria isso quando tem o operador LIKE
?
SELECT DISTINCT
first_name
FROM employees
WHERE first_name LIKE 'A%'
A mágica aqui está na cláusula `WHERE first_name LIKE ‘A%’`
, que significa "encontrar todos os first_name
que começam com "A" e terminam com qualquer número de caracteres". O `A%`
aqui é conhecido como um padrão para correspondência.
O `%`
não é o único curinga que você pode usar em conjunto com o operador LIKE
. Você também pode usar o sinal de sublinhado `_`
:
`%`
corresponde a qualquer número de caracteres.`_`
corresponde a qualquer caractere único.
A sintaxe é fácil de lembrar. É simplesmente isso:
column_name LIKE pattern
Você pode usar o site LIKE
para obter uma variedade de combinações de padrões. Veja como:
Exemplos de SQL LIKE para iniciantes
Abaixo, descrevemos alguns exemplos práticos de como você pode usar a declaração LIKE
e os resultados do nosso conjunto de dados de amostra.
1. Use LIKE para correspondência exata de strings
Se você quiser fazer uma correspondência exata de strings, use LIKE sem ‘%’
ou ‘_’
SELECT
first_name, last_name
FROM employees
WHERE first_name LIKE 'Barry' -- the same as WHERE first_name = ‘Barry’
2. Use '%' para corresponder a qualquer número de caracteres
‘%’
pode ser usado para corresponder a qualquer número (mesmo zero) de caracteres - um número, um alfabeto ou um símbolo.
Suponha que você queira encontrar todos os funcionários cujo nome comece com ‘Adam’
; você pode usar o padrão ‘Adam%’
SELECT DISTINCT
first_name
FROM employees
WHERE first_name LIKE 'Adam%'
Para encontrar nomes que terminam com "z", tente o padrão ‘%z’
. Você também pode usar vários ‘%’
em um padrão. Por exemplo, se você quiser encontrar nomes que contenham "z", use ‘%z%’
.
3. Use '_' para corresponder a um (e somente um) caractere
Assim como no jogo da forca, o sinal de sublinhado _
só pode conter um caractere.
De quantas maneiras você pode soletrar Le_n
? O padrão corresponderia a qualquer coisa entre "Lexn", "LeAn", "Le3n" ou "Le-n".
SELECT DISTINCT
first_name
FROM employees
WHERE first_name LIKE 'Le_n'
Quais são os nomes diferentes com apenas três caracteres? Você pode descobrir isso usando três sublinhados consecutivos ___
como padrão.
SELECT DISTINCT
first_name
FROM employees
WHERE first_name LIKE '___'
4. Use tanto '%' quanto '_' para corresponder a qualquer padrão
Obviamente, você pode usar os sites ‘%’
e ‘_’
para criar padrões interessantes.
SELECT DISTINCT
first_name
FROM employees
WHERE first_name LIKE '%ann_'
O padrão ‘%ann_’
corresponde a uma cadeia de caracteres que começa com qualquer número de caracteres e termina com "ann" e um outro caractere.
5. Use NOT para localizar cadeias de caracteres que não correspondam a um padrão
E se você quiser encontrar todas as linhas que não correspondem a um critério específico? Você pode usar o operador NOT LIKE
. Por exemplo, para localizar todos os títulos, exceto "Staff", podemos usar a sintaxe
`WHERE title NOT LIKE ‘Staff’`
Isso é exatamente equivalente à sintaxe
`WHERE title != ‘Staff’`
SELECT DISTINCT
title
FROM titles
WHERE title NOT LIKE 'Staff'
Obviamente, você pode usar o NOT LIKE
com qualquer um dos padrões que descrevemos.
SELECT DISTINCT
title
FROM titles
WHERE title NOT LIKE '%engineer'
6. Use LOWER (ou UPPER) com LIKE para correspondência de padrão sem distinção entre maiúsculas e minúsculas
Você pode usar a sintaxe a seguir se precisar fazer a correspondência de padrões, mas não tiver certeza se a cadeia de caracteres está armazenada em letras minúsculas, maiúsculas ou mistas.
`LOWER(column_name) LIKE pattern`
A função LOWER()
retorna todas as cadeias de caracteres em minúsculas, independentemente de estarem armazenadas em maiúsculas, minúsculas ou mistas.
Ao usar a sintaxe, certifique-se de que você escreve o padrão com todas as letras minúsculas! Caso contrário, você pode não obter nenhuma correspondência.
Você também poderia substituir LOWER
por UPPER
na sintaxe acima. Não se esqueça de escrever o padrão em letras maiúsculas.
`UPPER(column_name) LIKE PATTERN`
Por exemplo, para descobrir se o nome de um funcionário é "Joanne", "JoAnne", "Joanna" ou "JoAnna", tente uma das seguintes opções:
SELECT DISTINCT
first_name
FROM employees
WHERE lower(first_name) LIKE 'joann_'
SELECT DISTINCT
first_name
FROM employees
WHERE UPPER(first_name) LIKE 'JOANN_'
7. SQL LIKE com vários valores usando OR/AND
Você também pode combinar várias condições usando a sintaxe LIKE
.
Por exemplo, use a condição OR
para encontrar resultados que satisfaçam pelo menos um dos vários padrões LIKE
.
SELECT DISTINCT
first_name
FROM employees
WHERE
first_name LIKE 'Ad_l' OR
first_name LIKE 'Ad_m'
Por outro lado, para localizar uma cadeia de caracteres que corresponda a várias condições LIKE
, use a palavra-chave AND
.
SELECT DISTINCT
first_name
FROM employees
WHERE
first_name LIKE '%am%' AND
first_name LIKE '%me%'
A sintaxe LIKE
pode ser aplicada a várias colunas, desde que seu tipo de variável seja um caractere de comprimento variável (varchar
). Sabendo que podemos encontrar os nomes dos funcionários cujas iniciais são "Z. Z."
SELECT DISTINCT
first_name, last_name
FROM employees
WHERE
first_name LIKE 'Z%' AND
last_name LIKE 'Z%'
8. Use LIKE na cláusula SELECT CASE WHEN
Até agora, nos concentramos em usar LIKE
como uma condição para selecionar registros na cláusula WHERE
. Também usamos LIKE
na cláusula SELECT
. Por exemplo, podemos descobrir quantos funcionários chamados "Adam" existem em nosso banco de dados?
SELECT
COUNT(CASE WHEN first_name LIKE 'Adam' THEN 1 END) num_employees_adam
FROM employees
Por outro lado, quantos funcionários têm as iniciais "A.Z."?
SELECT
COUNT(CASE WHEN first_name LIKE 'A%' AND last_name LIKE 'Z%' THEN 1 END) num_employees
FROM employees
Padrões comuns usados no SQL LIKE
Aqui está um resumo dos padrões que discutimos para que você tenha uma referência rápida:
Padrão | Descrição | Exemplo de caso de uso |
---|---|---|
A% | Corresponde a cadeias de caracteres que começam com "A" | Como encontrar nomes que comecem com "A" |
%z% | Corresponde a cadeias de caracteres que contêm "z" | Encontrar nomes que contenham "z" |
Le_n | Corresponde a cadeias de caracteres como "Len", "Leon", etc. | Encontrar nomes com uma única variação de caracteres |
%ann_ | Corresponde a cadeias de caracteres que terminam com "ann" e um caractere adicional | Encontrar nomes como "Joann", "Joanna" |
Exemplos intermediários de SQL LIKE
A função LIKE
é bastante semelhante em diferentes tipos de SQL (por exemplo, PostgreSQL, MySQL, Redshift etc.). No entanto, alguns têm variações adicionais da função LIKE
que vale a pena mencionar.
1. O operador ILIKE
Disponível no Redshift e no PostgreSQL, ILIKE
é a versão sem distinção entre maiúsculas e minúsculas de LIKE
. Dessa forma, todos os itens a seguir são equivalentes.
SELECT
datacamp ILIKE ‘datacamp’, -- returns TRUE
DATACAMP ILIKE ‘datacamp’, -- returns TRUE
Datacamp ILIKE ‘datacamp’, -- returns TRUE
datacamp ILIKE ‘DataCamp’, -- returns TRUE
2. Usando colchetes [] e [^] como caracteres curinga
Os usuários do T-SQL ou do SQL Server têm caracteres curinga adicionais para uma correspondência de padrões mais complexa.
A sintaxe de colchetes []
corresponde a qualquer caractere específico dentro do intervalo ou conjunto. Por exemplo, todos os itens a seguir retornarão TRUE
.
SELECT
‘Carson’ LIKE ‘[C-K]arson’, -- returns TRUE because C is in the range C-K
‘Karson’ LIKE ‘[C-K]arson’, -- returns TRUE because K is in range
‘Larson’ LIKE ‘[CKL]arson’, -- returns TRUE because L is in the set [CKL]
‘Parson’ LIKE ‘[C-K]arson’ -- returns FALSE because P is out of range
O curinga caret-in-square-bracket [^]
corresponde a qualquer caractere único que não esteja dentro do intervalo ou conjunto especificado. Você consegue entender por que os resultados a seguir são esses?
SELECT
‘Carson’ LIKE ‘[^C-K]arson’ -- returns FALSE
‘Parson’ LIKE ‘[^C-K]arson’ -- returns TRUE
Aqui, como "C" está dentro do intervalo de [C-K]
, o padrão ‘C’
não corresponderá a [^C-K]
. Portanto, "Carson" não corresponderá a [^C-K]arson
, mas "Parson" corresponderá.
3. O operador RLIKE
Disponível no MySQL, o operador RLIKE
reconhece expressões regulares (RegEx) no padrão. O RegEx é uma ferramenta poderosa e versátil para correspondência avançada de padrões.
Não custa nada você ter um conhecimento básico de RegEx, especialmente se a variante de SQL que você usa for compatível com RLIKE. Você pode aprender mais sobre RegEx com nosso curso Expressões regulares em Python.
SELECT DISTINCT
first_name
FROM employees
WHERE first_name RLIKE 'Susann[a-e]'
4. O operador "~~
No PostgreSQL, ‘~~’
é completamente sinônimo de LIKE
. Há também equivalentes de ILIKE
, NOT LIKE
e NOT ILIKE
.
Operador |
Equivalente |
|
|
|
|
|
|
|
|
Solução de problemas: Erros e enganos comuns com o LIKE
Aqui estão alguns erros comuns que você pode encontrar ao usar o site LIKE
e como corrigi-los:
- Nenhum resultado foi retornado: Verifique a sensibilidade de maiúsculas e minúsculas. Em alguns dialetos SQL,
LIKE
diferencia maiúsculas de minúsculas por padrão. UseLOWER()
ouILIKE
(se suportado) para correspondência sem distinção entre maiúsculas e minúsculas. - Resultados inesperados com curingas: Certifique-se de que
%
e_
sejam usados corretamente.%
corresponde a qualquer número de caracteres, enquanto_
corresponde a exatamente um caractere. Se você os colocar em um lugar errado, poderá ter problemas inesperados. - Problemas de desempenho: Se sua consulta estiver lenta, procure por
%
em padrões (por exemplo,%pattern
), o que desativa o uso do índice. Reescreva os padrões para que comecem com uma cadeia de caracteres específica, se possível. Consulte a seção abaixo para saber mais sobre desempenho. - Erros de injeção de SQL: Se você estiver usando a entrada do usuário em sua consulta, certifique-se de que ela esteja devidamente parametrizada para evitar erros de sintaxe ou vulnerabilidades.
- Caracteres especiais em padrões: Símbolos curinga, como
%
e_
, na cadeia de pesquisa devem ser escapados se forem usados como caracteres literais. UseESCAPE
em sua consulta para definir um caractere de escape.
WHERE column_name LIKE '50\% OFF' ESCAPE '\'
Considerações sobre o desempenho ao usar o operador LIKE
O operador LIKE
é excelente, mas pode afetar o desempenho da consulta, especialmente quando usado em grandes conjuntos de dados. Aqui estão algumas considerações para otimizar seu uso:
- Índices: O operador
LIKE
tem melhor desempenho quando o padrão começa com uma cadeia de caracteres constante, comoAdam%
, porque o banco de dados pode usar índices. No entanto, padrões como%Adam
ou%Adam%
exigem uma varredura completa da tabela, o que pode ser lento em tabelas grandes. - Evite os curingas iniciais: Iniciar um padrão com
%
, como%pattern
, desativa o uso do índice, pois o banco de dados precisa examinar cada registro. - Colação e correspondência sem distinção entre maiúsculas e minúsculas: O uso de funções como
LOWER()
ouUPPER()
em colunas para pesquisas sem distinção entre maiúsculas e minúsculas também pode impedir que os índices sejam usados. Em vez disso, verifique se o agrupamento do banco de dados está definido adequadamente para comparações sem distinção entre maiúsculas e minúsculas. - Abordagens alternativas: Para grandes conjuntos de dados, considere o uso de pesquisa de texto completo ou de recursos de pesquisa específicos do banco de dados, como índices
GIN
no PostgreSQL ou índicesFULLTEXT
no MySQL, ao realizar correspondências de strings complexas ou frequentes. - Consultas seletivas: Limite o escopo de suas consultas usando filtros adicionais, como intervalos de datas ou colunas numéricas, para reduzir os dados processados pelo operador
LIKE
.
LIKE vs. Pesquisa de texto completo
A pesquisa de texto completo, disponível em bancos de dados como MySQL, PostgreSQL e SQL Server, é otimizada para consultas avançadas de texto, como pesquisa de palavras-chave, manipulação de variações linguísticas e classificação de resultados por relevância. Embora o site LIKE
seja ótimo para tarefas básicas, a pesquisa de texto completo é melhor para a pesquisa de texto escalonável e sofisticada.
Use o SQL LIKE com confiança
Dominar as funções SQL é fundamental para você ter sucesso na ciência de dados, e o comando LIKE
do SQL não é exceção. Um bom domínio do SQL aumentará muito o progresso de suas análises, portanto, não deixe de aprendê-lo bem!
Para obter mais recursos sobre SQL, não deixe de conferir o seguinte:
Torne-se um engenheiro de dados
Perguntas frequentes
O operador LIKE pode ser usado com tipos de dados numéricos?
Não, o operador LIKE
é usado especificamente para correspondência de padrões com tipos de dados de cadeia de caracteres, como CHAR
,VARCHAR
e TEXT
. Para tipos de dados numéricos, outros operadores de comparação, como =
,<
,>
, etc., são usados.
Como o desempenho do LIKE se compara ao de outros operadores SQL?
O operador LIKE
pode ser menos eficiente, especialmente com padrões que começam com %
, pois exige uma varredura completa da tabela. A indexação não pode ser usada com eficácia nesses casos, o que pode tornar as consultas mais lentas em grandes conjuntos de dados.
Você tem algum problema de segurança ao usar LIKE no SQL?
Embora o siteLIKE
em si não seja inerentemente inseguro, o uso de entradas de usuário diretamente em consultas SQL pode levar a ataques de injeção de SQL. Sempre higienize as entradas e considere o uso de consultas parametrizadas para reduzir esse risco.
Como você lidaria com pesquisas que diferenciam maiúsculas de minúsculas em uma variante do SQL que não oferece suporte a ILIKE?
Nos sabores de SQL sem ILIKE
você pode usar LOWER(column_name) LIKE LOWER(pattern)
ou UPPER(column_name) LIKE UPPER(pattern)
para realizar pesquisas sem distinção entre maiúsculas e minúsculas.
O LIKE pode ser usado com caracteres não ASCII?
Sim, oLIKE
pode ser usado com caracteres não ASCII, desde que a codificação do banco de dados seja compatível com esses caracteres. Isso inclui UTF-8, que é comumente usado para suportar uma ampla variedade de caracteres.
Como você modificaria uma consulta LIKE para pesquisar um caractere curinga literal (por exemplo, % ou _)?
Para pesquisar um literal %
ou _
, você precisa usar um caractere de escape. Por exemplo, no SQL Server, você pode usar LIKE 'A[%]%' ESCAPE '%'
para pesquisar cadeias de caracteres que contenham um literal %
.
Você pode combinar LIKE com outras funções SQL para melhorar os recursos de pesquisa?
Sim, a combinação de LIKE
com funções como CONCAT
ou SUBSTRING
pode ajudar a refinar os padrões de pesquisa. Por exemplo, usando CONCAT
você pode construir padrões dinamicamente com base em outros valores de coluna.
Como o LIKE pode ser usado em conjunto com as operações JOIN?
LIKE
pode ser aplicado nas condições do siteJOIN
para fazer a correspondência de padrões entre colunas de tabelas diferentes. Por exemplo, ON table1.col1 LIKE table2.col2 || '%'
pode ser usado para unir tabelas em que table1.col1
começa com table2.col2
.
Quais são algumas alternativas ao LIKE para correspondência de padrões complexos?
Para padrões mais complexos, você pode usar as variantes de SQL que suportam expressões regulares, como RLIKE
do MySQL ou SIMILAR TO
do PostgreSQL . Eles oferecem uma sintaxe mais rica para correspondência avançada de padrões.
Como o LIKE lida com valores nulos nas colunas?
Quando uma coluna contém valores nulos, o siteLIKE
não corresponderá a esses registros, pois os nulos não são considerados iguais a nenhuma cadeia de caracteres ou padrão. Para incluir nulos, use uma condição como OR column IS NULL
.
Saiba mais sobre o SQL
curso
Unindo dados no SQL
curso