Um guia completo para trabalhar com subcadeias de caracteres em SQL

Neste artigo, discutiremos como trabalhar com substrings no SQL. Isso envolve o aprendizado de várias abordagens, portanto, mostraremos suas vantagens e desvantagens.

Para trabalhar com substrings no SQL, primeiro precisamos saber o que é uma string. Em programação, uma cadeia de caracteres é qualquer tipo de dado textual (um título, uma descrição, um nome). Uma cadeia de caracteres consiste em qualquer número e tipo de caracteres. Em um banco de dados SQL, as cadeias de caracteres são normalmente armazenadas como tipos de dados CHAR ou VARCHAR.

Uma substring é parte de uma cadeia de caracteres. Em outras palavras, uma substring é uma parte menor da sequência de caracteres. Se você tivesse a string "Curso de SQL", "SQL" seria uma substring dessa string. Ilustramos isso na imagem abaixo:

Observe que a imagem (e uma string) não ignora o espaço em branco; os espaços em branco também são caracteres em uma string.

Prepare-se para trabalhar com subcadeias de caracteres SQL

Conhecer a família de funções de texto do SQL é obrigatório ao trabalhar com strings. Embora mantenhamos os exemplos de substring o mais simples possível, precisaremos de funções de texto. O uso dessas funções geralmente requer o conhecimento de outros conceitos de SQL, como JOINs, agrupamento de dados, filtragem de dados e uso de subconsultas. Você pode praticar todos esses conceitos em nossa Trilha de Práticas em SQL trilha. Há oito cursos práticos com um total de mais de 950 desafios de codificação, portanto, seus dedos vão doer até o final deste curso :) Mas esse é o objetivo, pois a prática é realmente a única maneira de se tornar fluente em SQL.

Usaremos a mesma abordagem aqui e mostraremos a você o máximo de exemplos que este artigo permitir. Ao extrair uma substring de uma cadeia de caracteres no SQL, a função de texto de nome conspícuo SUBSTRING() parece ser uma escolha óbvia. Entretanto, ela não é a única opção! Há também outras, como o operador LIKE e as funções de texto LEFT() e RIGHT().

Embora isso possa parecer fácil, trabalhar com substrings SQL nem sempre é simples. Você precisa conhecer as nuances de cada função que deseja aplicar, como funciona a indexação no SQL e como o SQL lida com dados textuais. Muitos profissionais de dados confirmarão que trabalhar com substrings em SQL pode se tornar uma experiência dolorosa e frustrante!

Bem, vamos garantir que não seja dolorosa para você. Vamos começar.

Conjunto de dados de exemplo

Trabalharemos com a tabela clients. Como o nome sugere, é uma lista dos clientes da empresa.

Você pode criar a tabela usando esta consulta. Observe que a data de nascimento é armazenada em um formato de dados de texto.

Vamos começar extraindo substrings!

Verificar se uma string contém uma substring no SQL

Uma maneira comum de verificar se um texto contém uma determinada substring no SQL é usar o operador LIKE ou ILIKE. Eles são usados com a cláusula WHERE para filtrar dados. Ambos os operadores encontrarão cadeias de caracteres com uma substring como a definida em seu padrão. A única diferença é que LIKE diferencia maiúsculas de minúsculas, enquanto ILIKE não diferencia.

Use LIKE e ILIKE para verificar se o texto contém uma substring

Seu colega lhe diz que um cliente ligou com uma mensagem para você. Como o colega estava com pressa, ele se esqueceu de anotar o nome do cliente. O nome do cliente era Isaac, ou Dave, ou algo do gênero.

Você poderia tentar localizar o cliente chamado Isaac ou Dave escrevendo esta consulta:

SELECT *
FROM clients
WHERE first_name LIKE 'Dave' OR first_name LIKE 'Isaac';

Nessa consulta, você seleciona todas as colunas da tabela. Em seguida, você usa a cláusula WHERE e o operador LIKE. Você coloca os nomes 'Dave' e 'Isaac' entre aspas simples no operador LIKE. A condição na cláusula WHERE pode ser lida como: "Return all the clients with Dave or Isaac as a first name".

Vamos executar o código e ver... que não há absolutamente nada! O código não retorna nenhuma linha. Código estúpido! Se olharmos a tabela, veremos que há clientes chamados Isaac Guardiola e Dave Trotter:

Então, por que isso não funciona? A consulta acima assumirá que a substring começa com "Dave" ou "Isaac". Observando a tabela, podemos ver que a cadeia de caracteres começa, na verdade, com a saudação.

Para que essa consulta funcione, devemos usar o curinga '%' com LIKE. Esse curinga é usado para pesquisar uma substring no início, no final e em qualquer lugar no meio. Tudo depende de onde você posiciona o curinga. Aqui está uma breve visão geral de como usá-lo.

Vamos reescrever a consulta:

SELECT *
FROM clients
WHERE first_name LIKE '%Dave%' OR first_name LIKE '%Isaac%';

Aprendemos que os curingas antes e depois da substring que queremos encontrar significam que a consulta procurará essa substring em qualquer lugar da string. Isso nos permite ignorar a saudação na frente de cada nome.

Aqui está o resultado:

Para dar outro exemplo do uso de '%' com LIKE, você também pode procurar todos os senhores em nossa lista de clientes.

Para fazer isso, coloque o curinga após a substring que está pesquisando, pois a substring começa com "Mr.":

SELECT *
FROM clients
WHERE first_name LIKE 'Mr.%';

Aqui está o resultado.

Você deve ter notado que escrevemos as substrings em LIKE exatamente como estão escritas na tabela referente à capitalização. Isso ocorre porque o operador LIKE diferencia maiúsculas de minúsculas.

Se quiser evitar isso ou não tiver certeza de como os dados estão formatados, você pode usar o operador ILIKE. Como ele não diferencia maiúsculas de minúsculas, você pode escrever a substring da maneira que quiser:

SELECT *
FROM clients
WHERE first_name ILIKE '%dave%' 
OR first_name ILIKE '%iSAaC%';

A saída é a mesma que a do LIKE:

Você pode saber mais sobre LIKE (e NOT LIKE) neste artigo.

A função SUBSTRING()

SUBSTRING() é uma função SQL usada para extrair a substring de uma cadeia de caracteres. Ela permite que você especifique a string da qual deseja extrair a substring. Você define a substring especificando sua posição inicial dentro da string e seu comprimento.

Veja a seguir a sintaxe da função:

SUBSTRING(string, start_position, length)

Como qualquer função SQL, SUBSTRING() recebe argumentos entre parênteses. Dois argumentos são obrigatórios:

• string - A cadeia de caracteres que você deseja pesquisar; ela pode ser uma constante ou uma coluna/expressão.
• start_position - Um valor inteiro que define a posição (o número ordinal do caractere) em que a substring começa, ou seja, a posição do primeiro caractere da substring.

O terceiro argumento é length, que é opcional. Ele define o comprimento de uma substring como o número de caracteres que ela conterá. Quando esse argumento é omitido, a substring será o restante da string, contando a partir da posição inicial.

Vejamos alguns exemplos para esclarecer tudo isso.

Use SUBSTRING() para extrair uma substring de uma cadeia de caracteres constante

Uma cadeia de caracteres constante é qualquer cadeia de caracteres que você escreve entre aspas simples como o primeiro argumento em SUBSTRING().

Por exemplo, a string constante no código a seguir é 'Trilha de Práticas em SQL Course', e queremos extrair 'Practice Course'.

SELECT SUBSTRING ('Trilha de Práticas em SQL Course', 5) 
AS substring_extract

O número inteiro 5 é o argumento start_position. Isso significa que a substring começa a partir do quinto caractere da string.

Por que escolher 5 se há apenas três letras ('SQL') na frente de nossa substring? A substring não deveria começar na quarta posição? Tome cuidado: O espaço em branco também conta como um caractere em uma string!

Como omitimos o argumento length, o código retornará o restante da string como uma substring. Em outras palavras, o código retorna esse resultado:

Use SUBSTRING() para extrair uma substring de uma coluna

Em vez de um valor constante, você também pode especificar uma coluna da qual deseja extrair uma substring.

Vamos mostrar o nome e o sobrenome do cliente, mas sem a saudação (Mr./Ms./Mx.):

SELECT SUBSTRING (first_name, 5) AS first_name,
	 last_name
FROM clients;

Novamente usamos a função SUBSTRING(). O primeiro argumento é o nome da coluna, que é first_name. O segundo argumento é a posição inicial da substring, que é cinco. Lembre-se de contar os espaços em branco! Há um após a saudação, portanto, 'Mr. ' consiste em quatro caracteres. O quinto caractere é a primeira letra do nome do cliente.

Omitimos o comprimento da substring e a segunda coluna selecionada é o sobrenome do cliente, portanto o código retorna isso:

Usar SUBSTRING() para extrair uma substring de um comprimento definido

Agora deixaremos de evitar o terceiro argumento SUBSTRING(). Neste exemplo, mostraremos como extrair o dia e o mês de nascimento dos clientes:

SELECT first_name,
	 last_name,
	 SUBSTRING (date_of_birth, 1, 6) AS birthday
FROM clients;

Depois de selecionar o nome e o sobrenome dos clientes, usamos a função SUBSTRING(). Usaremos a coluna date_of_birth para extrair a data de nascimento do cliente (mês e dia).

Depois de passar a coluna como argumento, especificamos a posição inicial. Esse é o número 1, o que significa que a substring será extraída do primeiro caractere da string. O número 6 significa que a substring consistirá em seis caracteres: dois caracteres para um dia, um ponto, dois caracteres para um mês e outro ponto.

Dê uma olhada no resultado:

Todos os clientes estão lá, junto com seus aniversários, extraídos da maneira que queríamos. Não nos importamos em mostrar a saudação dessa vez.

Uso de SUBSTRING() com outras funções para localizar um índice de substring

Há funções que podem ser usadas para localizar um índice dentro de uma cadeia de caracteres. No MySQL e no PostgreSQL, essa função é chamada de POSITION(); no SQL Server, é chamada de CHARINDEX(). Você pode especificar a substring que está procurando e essas funções retornarão sua posição dentro da cadeia de caracteres.

Essas funções são extremamente úteis em combinação com SUBSTRING(). Vamos discutir primeiro POSITION() e, em seguida, veremos um exemplo com CHARINDEX().

A função POSITION() permite que você especifique a substring e ela encontrará sua posição inicial. Vamos usá-la para extrair o nome de usuário de cada cliente de seu endereço de e-mail:

SELECT first_name,
	 last_name,
	 email,
	 POSITION('@' IN email) AS at_position,
	 SUBSTRING(email, 1, POSITION('@' IN email)-1) AS username
FROM clients;

Acima, usamos POSITION() para localizar o '@' no endereço de e-mail de cada usuário. Isso é necessário porque tudo o que vem antes desse caractere é o nome do usuário. A sintaxe de POSITION() é simples: basta especificar o caractere de pesquisa entre aspas simples e, em seguida, a palavra-chave IN e o nome das colunas que você deseja procurar.

Essa coluna retorna apenas a posição do caractere. Precisamos incorporá-la em SUBSTRING() para obter o nome de usuário. Os dois primeiros argumentos em SUBSTRING() são familiares: estamos olhando para a coluna email e queremos que nossa substring (um nome de usuário) comece com o primeiro caractere da string.

O terceiro argumento em SUBSTRING() é o comprimento da substring. O comprimento do nome de usuário é o número de caracteres antes e depois de '@'. Em outras palavras, o comprimento do nome de usuário é a posição de '@' menos um. Por que menos? Porque, caso contrário, '@' faria parte do nome de usuário.

Podemos verificar o resultado. Em 'iguardiola@gmail.com', o símbolo '@' está na posição 11, conforme mostrado na coluna at_position. A coluna nome de usuário mostra a substring antes de '@', que é de fato 'iguardiola' para esse cliente. Você pode verificar o restante dos resultados da mesma forma.

O mesmo código no SQL Server deve usar a função CHARINDEX():

SELECT first_name,
	 last_name,
	 email,
	 CHARINDEX('@', email) AS at_position,
	 SUBSTRING(email, 1, CHARINDEX('@', email)-1) AS username
FROM clients;

A lógica é semelhante à da função POSITION(): você especifica a substring que está procurando e, em seguida, a coluna, com os argumentos separados por vírgula.

A saída do código é a mesma.

Terminamos agora com SUBSTRING(). Mas se você precisar de mais prática, aqui estão mais cinco exemplos de SUBSTRING().

LEFT() e RIGHT() em SQL

Outra forma de extrair uma substring é usar as funções LEFT() ou RIGHT().

LEFT() A função LEFT( extrai a substring da esquerda, o que nos permite definir o comprimento da substring. A função RIGHT() faz a mesma coisa, mas do lado direito.

Uso de LEFT() e RIGHT() para extrair uma substring

Vamos usar essas duas funções para extrair algumas substrings: as três primeiras letras de um sobrenome e o ano de nascimento.

Aqui está o código.

SELECT first_name,
	 last_name,
	 LEFT(last_name, 3) AS last_name_substring,
	 RIGHT(date_of_birth, 4) AS year_of_birth
FROM clients;

Usamos a função LEFT() para obter as três primeiras letras do sobrenome. Ela começa da esquerda e usa o número especificado de caracteres para criar uma substring. A sintaxe é simples: especificamos o nome da coluna e, em seguida, o comprimento da substring.

RIGHT() é usado para encontrar o ano de nascimento, começa à direita e usa o número especificado de caracteres para criar uma substring. Aqui, o primeiro argumento é a coluna date_of_birth. Em seguida, contamos o número de caracteres que queremos da direita, que é quatro - isso retornará o ano de nascimento.

Como LEFT() e RIGHT() são basicamente imagens espelhadas uma da outra, poderíamos facilmente ter usado qualquer uma dessas funções para encontrar as duas substrings necessárias.

Aqui está o resultado da consulta acima:

Ela mostra as três primeiras letras do sobrenome e o ano de nascimento.

Torne-se a pessoa que extrai todas as (sub)cadeias de caracteres no SQL

Como analista de dados, espera-se que você puxe todas as (sub)cordas sozinho. Um dos truques para conseguir isso é conhecer várias maneiras de trabalhar com substrings, como as que mostramos aqui.

O conhecimento de diferentes funções para lidar com substrings permite que você escolha a que melhor se adapta ao seu problema. Além de obter o resultado desejado, você também terá um código mais eficiente e com menos linhas.

Para chegar a esse nível, você deve trabalhar muito com strings e usar todas as funções mencionadas diariamente. Em outras palavras, você precisa escrever código. Se você precisa de um ambiente seguro para fazer isso, nosso Trilha de Práticas em SQL curso oferece exatamente isso.

Boa sorte ao se tornar o mestre das substrings SQL!