A função SQL Substring em 5 exemplos

Trabalhando com dados de texto em SQL? Explicamos como obter valores a partir de qualquer ponto de uma cadeia de caracteres.

Quando você pensa em trabalhar com dados em SQL, seu primeiro pensamento provavelmente é um banco de dados cheio de números e seu código SQL fazendo cálculos muito extravagantes. Mas texto também são dados! É muito comum encontrar dados de texto em bancos de dados. Você não só tem que extraí-los, mas muitas vezes também tem que manipulá-los. As funções que lhe permitem fazê-lo são chamadas funções de texto.

Para qualquer um que queira praticar as funções SQL, eu recomendo nossa Funções Comuns em SQL curso. Ele contém 211 exercícios e ensina como usar o texto comum, numéricos e funções de data e hora em SQL.

Uma das funções de texto comuns que o curso abrange é SUBSTRING(). Neste artigo, temos cinco exemplos de negócios da vida real que cobrem os principais usos desta função. Alguns exemplos podem parecer complicados se você não estiver familiarizado com as funções de texto, então certifique-se de ter ao seu lado a folha de consultas Funções Comuns em SQL ou uma visão geral das funções de texto SQL.

Qual é a função SUBSTRING()?

SUBSTRING() é uma função de texto que lhe permite extrair caracteres de uma string. Sua sintaxe é

SUBSTRING(expression, start, length)

Para o argumento expression, você escreve uma corda literal ou especifica uma coluna da qual você quer extrair o substrato. O argumento start é um número inteiro indicando a posição numérica do caractere na cadeia de caracteres onde começa o substrato. O argumento length, como o nome diz, define o comprimento, um valor inteiro, do substring a ser devolvido.

Como funciona o SUBSTRING()?

A dica está no próprio nome da função. Um substring é uma cadeia dentro da cadeia principal. Portanto, SUBSTRING() extrai um substring como você especifica em seu argumento.

Funciona desta forma:

Na string acima, o substrato que começa na posição 1 e tem um comprimento de três caracteres é ‘STR’.

Agora que temos os princípios cobertos, deixe-me mostrar-lhe vários exemplos. Começando, é claro, com o mais simples!

Exemplo 1: Substring From a String Literal

A função SUBSTRING() retorna um substrato de qualquer corda que você desejar. Você pode escrever a corda explicitamente como um argumento, como este:

SELECT SUBSTRING('This is the first substring example', 9, 10) AS substring_extraction;

Isto significa: Quero encontrar um substrato a partir do texto ‘This is the first substring example’. Os argumentos dizem que o substring começa no 9º caracter da cadeia de caracteres e que seu comprimento é de 10 caracteres.

Vamos ver o que este código retorna:

Há uma coluna e uma linha. O substrato extraído é ‘the first’. Este é o uso mais básico do SUBSTRING(); o código não usa nem mesmo tabelas!

A Tabela dos Funcionários

Para mostrar-lhe exemplos mais interessantes, preciso de alguns dados. Deixe-me apresentá-lo a uma tabela chamada employees.

A tabela armazena informações sobre os funcionários de uma empresa imaginária Kooler nas seguintes colunas:

• id - A identificação do funcionário.
• first_name - O primeiro nome do funcionário.
• last_name - O sobrenome do funcionário.
• email - O e-mail do funcionário.
• job_title - O cargo do funcionário.
• department - O departamento do funcionário.
• start_date - A data de início do funcionário em Kooler.

Aqui estão as primeiras várias linhas para que você tenha uma noção dos dados:

Exemplo 2: Substring From a Column

Como você pode imaginar, escrever a expressão das cordas explicitamente não é a única maneira de usar SUBSTRING(). Você também pode utilizá-la em uma coluna de uma tabela.

Aqui está um exemplo disso. Eu quero encontrar as iniciais de todos os funcionários. Uso a coluna email, pois sei que as duas primeiras letras do endereço de e-mail são as iniciais:

SELECT first_name,
	 last_name,
	 email,
	 SUBSTRING(email, 1, 2) AS employee_initials
FROM employees;

Eu especifico a coluna email na função. Obter as duas primeiras letras do endereço de e-mail significa que o substrato começa no primeiro caractere para um comprimento de dois caracteres. Isto retorna o resultado desejado:

Exemplo 3: Substring Sem o Argumento de Comprimento

Você pode omitir o argumento da duração em SUBSTRING(), e a função ainda funciona. Um bom exemplo é quando você quer mostrar apenas o ano da data de início do emprego. Veja, a coluna start_date é um pouco antipática para isso. Esta data é escrita como dados de texto no formato MM/AAAA.

Felizmente, SUBSTRING() resolve este problema:

SELECT first_name,
	 last_name,
	 start_date,
	 SUBSTRING(start_date, 4) AS start_year
FROM employees;

Para obter o ano da coluna start_date, é suficiente definir o início do substrato. Neste código, o substring começa a partir do quarto caractere. Como eu omiti o argumento do comprimento, o comprimento do substring é longo até o final da cadeia a partir do quarto caractere. É assim que eu obtenho facilmente o ano, como você vê abaixo:

Exemplo 4: POSITION() e CHARINDEX()

Voltar ao trabalho com e-mails. Pela política da empresa, o ponto local de um endereço de e-mail (isto é, a parte antes de '@') também é o nome de usuário do funcionário para fazer login em todas as aplicações comerciais. Você precisa extrair este nome de usuário. Veja como:

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

Os dois primeiros argumentos são os que você já viu. Quero extrair um substrato da coluna email, e quero que ele comece a partir do primeiro caractere da cadeia. Mas agora, o comprimento do substrato é diferente para cada funcionário. Como posso dizer à função para retornar todos os caracteres antes do sinal "@"?

Eu uso POSITION(), que é equivalente a CHARINDEX() no SQL Server ou MySQL. Ele localiza o caractere especificado na string e retorna sua posição numérica de caracteres. Então, o comprimento do substrato que é o nome de usuário do funcionário é igual a POSITION('@' IN email)-1. Por que menos um? Porque não quero que '@' seja incluído no nome de usuário do funcionário.

Este é o resultado:

Exemplo 5: COMPRIMENTO() + POSIÇÃO()

O exemplo final mostra como encontrar o cargo de um funcionário a partir dos dados. Trabalhando na Kooler, sei como são formados os títulos de emprego: primeiro vem a antiguidade do funcionário, depois o departamento, depois o cargo. Por exemplo, 'Assistente de Vendas Júnior' significa que o funcionário é de senioridade júnior, está em Vendas, e trabalha como assistente.

Usando SQL, eu posso extrair isto como um substrato:

SELECT first_name,
       last_name,
       job_title,
       SUBSTRING(job_title, LENGTH(job_title) - POSITION(' ' IN REVERSE(job_title))+2) AS position
FROM employees;

Este é outro exemplo de omitir o argumento da extensão, embora um pouco mais complexo. Como sempre, eu primeiro especifico a coluna de cordas - job_title neste caso. Depois disso, de alguma forma preciso encontrar um substring que consista apenas na última palavra no título do trabalho.

Faço isto primeiro usando LENGTH(). Ele retorna o comprimento da cadeia de caracteres na coluna job_title. Isso é um começo; é o comprimento das três palavras juntas, incluindo os espaços em branco. Se eu pudesse de alguma forma subtrair dele o número de caracteres da última palavra, então eu teria o comprimento das duas primeiras palavras, o que me daria então o início do substrato que eu quero.

Isto é um pouco complicado porque nomes de cargos diferentes têm comprimentos diferentes. A única coisa que separa as palavras é o espaço em branco. Portanto, para obter o comprimento da terceira palavra na cadeia, tenho que contar o número de caracteres até o espaço em branco, mas da direita.

A função POSITION() salva o dia novamente, mas desta vez combinada com REVERSE(). A função REVERSE() inverte a expressão das cordas para que o 'Junior Sales Assistant' se torne o 'tnatsissA selaS roinuJ'. A última palavra se torna a primeira; a própria palavra também é invertida, mas isso não importa aqui.

POSITION() encontra a posição do espaço em branco após a primeira palavra da corda invertida. Isto é igual ao lugar do espaço em branco antes da última palavra na cadeia original (não invertida).

Phew! Agora, se eu subtrair este número do comprimento total da cadeia de caracteres original, eu tenho o início do substrato, certo?

Bem, não exatamente! Usando esta diferença, pois ela produz um substring que inclui a última letra da segunda palavra e o espaço em branco antes da última palavra. Por que isso acontece?

Duas coisas. O argumento inicial da função SUBSTRING() é inclusivo. Além disso, POSITION() calcula a posição do espaço em branco, não o número de caracteres até o espaço em branco. Portanto, tenho que adicionar 2 para obter este resultado:

Agora que introduzi algumas outras funções, você pode querer dar uma olhada em algumas outras funções de texto que podem ser úteis para você.

Saiba mais sobre SUBSTRING () e trabalhar com dados de texto

Agora você sabe quando e como usar SUBSTRING(). Chegou a hora de praticar!

Existem outras funções de texto, não apenas SUBSTRING(). Você pode encontrá-las (e muito mais!) no Funções Comuns em SQL curso.