SQL Funções de Texto

Os dados da Loja Aurora estão cheios de texto: nomes de clientes, emails, cidades, telefones, nomes de produtos. E quase nunca do jeito que a Ana precisa mostrar. Ela quer o primeiro nome do cliente para o email de aniversário, o domínio de cada email, o telefone só com números, a cidade e o estado juntos ("Teresina - PI") e o preço com duas casas ("4,90"). Tudo isso se faz no próprio SELECT, com as funções de texto. Nesta aula você vai conhecer as principais e as armadilhas de cada uma, incluindo a mais traiçoeira para quem escreve em português: o upper do SQLite não entende acentos.

Os exemplos rodam de verdade no seu navegador, num SQLite com o banco da Loja Aurora, e cada exemplo parte do banco limpo. Texto, em SQL, vai sempre entre aspas simples: 'Ana'.

A ideia: a tesoura, a cola e o carimbo

Pense no balcão da loja com uma ficha de papel de cada cliente. Para preparar as etiquetas de envio, a Ana usa três ferramentas:

• a tesoura, para recortar só um pedaço: o primeiro nome, o DDD do telefone (substr, trim);
• a cola, para juntar pedaços: cidade + " - " + estado (||, concat);
• os carimbos, que mudam a cara do texto sem mudar o conteúdo: tudo em maiúsculas, o preço com duas casas (upper, printf).

As funções de texto são essas ferramentas. Um detalhe importante: elas não mudam a tabela. Um SELECT upper(nome) FROM clientes mostra os nomes em maiúsculas, mas os nomes guardados continuam como estavam. Só um UPDATE (aula UPDATE) muda o que está guardado.

Toda função tem o mesmo molde: o nome, e entre parênteses o que ela recebe (os argumentos), separados por vírgula: substr(nome, 1, 3).

1. length: quantas letras

SELECT nome, length(nome) AS letras
FROM clientes
ORDER BY letras DESC
LIMIT 3;

length ("comprimento") conta os caracteres do texto, contando os espaços. O nome mais comprido da loja é "Gustavo Henrique Prado", com 22. As letras acentuadas contam como uma letra cada, como deveria ser:

SELECT nome,
       length(nome)       AS letras,
       octet_length(nome) AS bytes
FROM clientes
WHERE id IN (3, 5);

"Conceição Prado" tem 15 letras, mas ocupa 17 bytes no arquivo: o ç e o ã gastam 2 bytes cada (é assim que o formato UTF-8 guarda letras acentuadas). A octet_length ("comprimento em octetos", ou bytes) mostra isso. Para contar letras, use sempre a length.

2. upper e lower: maiúsculas e minúsculas

upper ("de cima", as letras maiúsculas) e lower ("de baixo", as minúsculas) trocam a caixa das letras. Com nomes sem acento, tudo certo:

SELECT upper('Ana Beatriz Sousa') AS maiusculas,
       lower('Ana Beatriz Sousa') AS minusculas;

Agora com os nomes acentuados da loja:

SELECT nome, upper(nome) AS maiusculas, lower(nome) AS minusculas
FROM clientes
WHERE id IN (1, 5, 9);

Olhe com atenção: JOSé RIBAMAR SILVA, CONCEIçãO PRADO, ANTôNIO CARLOS NEVES. As letras acentuadas não mudaram. O upper e o lower do SQLite só sabem trocar as 26 letras do alfabeto inglês, de a a z (o conjunto ASCII). O é, o ç, o ã e o ô passam direto, do jeito que estavam. É uma escolha do SQLite, para ficar pequeno: tratar maiúsculas e minúsculas de todas as línguas do mundo exige tabelas grandes.

SELECT 'Ana' LIKE 'ana'                AS sem_acento,
       'Água' LIKE 'água'              AS com_acento,
       upper('josé') = 'JOSÉ'          AS upper_com_acento,
       lower('ANA.BEATRIZ@EXEMPLO.COM.BR') AS email_minusculo;

Os resultados 1 e 0 são o "verdadeiro" e o "falso" do SQLite. O email ficou certinho em minúsculas: só tinha letras sem acento.

3. substr: recortar um pedaço

substr (de substring, "pedaço do texto") recorta um trecho. Ela recebe o texto, a posição onde começa e, se quiser, quantos caracteres levar. A primeira letra é a posição 1:

SELECT substr('Ana Beatriz Sousa', 1, 3)  AS do_1_leva_3,
       substr('Ana Beatriz Sousa', 5, 7)  AS do_5_leva_7,
       substr('Ana Beatriz Sousa', 5)     AS do_5_ate_o_fim,
       substr('Ana Beatriz Sousa', -5)    AS ultimas_5;

(O ␣ marca os espaços: eles também ocupam uma posição.)

substr(texto, 1, 3)

Começa na posição 1 e leva 3: Ana.

substr(texto, 5, 7)

Começa na posição 5 (o B) e leva 7: Beatriz.

substr(texto, 5)

Sem o terceiro argumento, vai até o fim: Beatriz Sousa.

substr(texto, -5)

Posição negativa conta do fim para o começo: as 5 últimas, Sousa.

O SQLite também aceita o nome substring, do SQL padrão, com os mesmos argumentos.

instr: onde está?

Para recortar o primeiro nome, a posição do corte muda de cliente para cliente: "Ana" tem 3 letras, "Carlos" tem 6. A instr (de in string, "no texto") diz em que posição um trecho aparece pela primeira vez, ou 0 se não aparece:

SELECT nome,
       instr(nome, ' ') AS posicao_do_espaco,
       substr(nome, 1, instr(nome, ' ') - 1) AS primeiro_nome
FROM clientes
WHERE id <= 4;

Em "José Ribamar Silva", o primeiro espaço está na posição 5. O primeiro nome vai da posição 1 e leva 5 − 1 = 4 letras: "José". O - 1 é para não levar o espaço junto. O mesmo truque separa as duas partes de um email:

SELECT email,
       substr(email, 1, instr(email, '@') - 1) AS usuario,
       substr(email, instr(email, '@') + 1)    AS dominio
FROM clientes
WHERE id <= 3;

O usuário é tudo antes do @ (até a posição dele − 1); o domínio é tudo depois (da posição dele + 1 até o fim).

SELECT nome AS primeiro_nome
FROM clientes;

SELECT substr(nome, 1, instr(nome, ' ') - 1) AS primeiro_nome
FROM clientes;

4. trim e replace: limpar e trocar

trim: tirar as sobras das pontas

Quem digita um cadastro às vezes deixa espaços sobrando no começo ou no fim: ' Ana '. Para o banco, 'Ana' e 'Ana ' são textos diferentes, e uma busca por 'Ana' não acha o segundo. A trim ("aparar") tira os espaços das duas pontas; ltrim só da esquerda (left) e rtrim só da direita (right). As barras | no exemplo estão lá só para você enxergar onde o texto acaba:

SELECT '|' || trim('   Ana   ')  || '|' AS com_trim,
       '|' || ltrim('   Ana   ') || '|' AS com_ltrim,
       '|' || rtrim('   Ana   ') || '|' AS com_rtrim,
       trim('--Ana--', '-')             AS outro_caractere;

Com um segundo argumento, a trim tira outros caracteres das pontas: trim('--Ana--', '-') tira os hífens. Ela nunca mexe no meio do texto: trim('Ana Lima') mantém o espaço entre os nomes.

replace: trocar um trecho por outro

replace ("substituir") troca todas as vezes que um trecho aparece. Ela recebe o texto, o que procurar e o que pôr no lugar:

SELECT replace('Caneta Esferográfica Azul', 'Azul', 'Preta') AS outra_cor,
       replace('banana', 'a', 'o')                            AS todas_as_vezes,
       replace('(86) 99821-4455', '-', '')                    AS sem_hifen;

Para deixar o telefone só com números, é preciso tirar quatro coisas: parênteses, espaço e hífen. Uma replace dentro da outra, de dentro para fora:

SELECT nome, telefone,
       replace(replace(replace(replace(telefone, '(', ''), ')', ''), ' ', ''), '-', '') AS so_numeros
FROM fornecedores;

Leia de dentro para fora: a replace mais de dentro tira o (; a de fora dela tira o ); depois o espaço; por último o hífen. Os telefones de Portugal e da Argentina ficaram com o + do código do país, porque ele não estava na lista.

5. Juntar textos: ||, concat e concat_ws

No SQL padrão, quem junta (ou concatena) textos é o operador ||, duas barras verticais. Ele cola o da esquerda com o da direita, sem pôr nada no meio; os espaços e separadores são por sua conta:

SELECT nome,
       cidade || ' - ' || estado AS local
FROM clientes
WHERE id <= 3;

Três pedaços colados: a cidade, o texto ' - ' (espaço, hífen, espaço) e o estado. Números também podem entrar na junção, e viram texto: 'Total: ' || 30.94 dá Total: 30.94.

O NULL estraga a junção inteira

Agora com os fornecedores. Dois deles são estrangeiros e estão com estado NULL:

SELECT nome, cidade, estado,
       cidade || ' - ' || estado AS local
FROM fornecedores;

Para a Lusitana Comércio (Porto) e a Pampa Sur (Buenos Aires), o local inteiro virou NULL, e a cidade, que existia, sumiu junto. É a regra do NULL (aula O Valor NULL): qualquer conta com um valor desconhecido dá desconhecido, e juntar textos é uma conta. Três jeitos de consertar:

SELECT nome,
       cidade || ' - ' || coalesce(estado, pais) AS com_coalesce,
       concat(cidade, ' - ', estado)             AS com_concat,
       concat_ws(' - ', cidade, estado)          AS com_concat_ws
FROM fornecedores;

coalesce(estado, pais)

Usa o estado; se ele for NULL, usa o país (aula IFNULL e COALESCE). Resultado: Porto - Portugal.

concat(a, b, c, …)

Junta todos os argumentos, tratando NULL como texto vazio. Não some com a cidade, mas sobra o separador: Porto - , com o hífen pendurado.

concat_ws(separador, a, b, …)

"Concat with separator", com separador. O primeiro argumento é o separador, posto entre os outros, e os NULL são pulados junto com o seu separador: Porto, limpinho.

SELECT nome,
       cidade || ' - ' || estado AS local
FROM fornecedores;

SELECT nome,
       concat_ws(' - ', cidade, estado) AS local
FROM fornecedores;

6. printf e format: carimbar números e textos

O SQLite escreve os números do jeito mais curto: o marca-texto custa 4.9, e não 4.90. Para uma etiqueta de preço, isso é feio. A printf ("imprima formatado", um nome que vem da linguagem C) recebe um molde e os valores que entram nele. O SQLite também a chama de format; as duas são a mesma função.

SELECT nome, preco,
       printf('%.2f', preco)                       AS duas_casas,
       format('R$ %.2f', preco)                    AS com_moeda,
       replace(printf('%.2f', preco), '.', ',')    AS jeito_brasileiro
FROM produtos
WHERE id IN (1, 4, 9, 14);

'%.2f'

Um número com 2 casas depois do ponto (f de float, número com vírgula). 4.9 vira 4.90; 99.0 vira 99.00.

'R$ %.2f'

O que está fora do código é copiado como está: o R$ fica na frente.

replace(…, '.', ',')

A printf usa ponto. Para o formato brasileiro, troque o ponto pela vírgula.

O resultado da printf é texto, pronto para mostrar. Não use esse texto para fazer contas nem para ordenar: para isso, use o número original (ou a round, da aula Funções Numéricas).

Os códigos da printf

SELECT printf('%05d', 42)                AS cinco_digitos,
       printf('%s tem %d anos', 'Ana', 30) AS frase,
       printf('%d%%', 15)                 AS porcentagem,
       printf('[%-6s]', 'Ana')            AS alinhado_esquerda,
       printf('[%6s]', 'Ana')             AS alinhado_direita;

Cada código pega o próximo valor da lista, na ordem: em printf('%s tem %d anos', 'Ana', 30), o %s pega 'Ana' e o %d pega 30.

7. Formas diferentes de fazer

Muitas tarefas de texto têm mais de uma solução. Para "cidade - estado, sem perder ninguém":

E para "o primeiro nome": substr(nome, 1, instr(nome, ' ') - 1) funciona para todo nome com espaço. Para nomes de uma palavra só, veja o primeiro caso de Quando dá errado.

Quando dá errado

SELECT instr('Maria', ' ')                        AS posicao,
       substr('Maria', 1, instr('Maria', ' ') - 1) AS primeiro_nome;

SELECT 'Ana' + ' Lima' AS com_mais,
       'Ana' || ' Lima' AS com_barras;

SELECT substr('Ana Beatriz', 0, 3) AS comecando_do_zero,
       substr('Ana Beatriz', 1, 3) AS comecando_do_um;

SELECT nome
FROM clientes
WHERE cidade = "Teresina";

SELECT nome, cidade
FROM fornecedores
WHERE nome = 'Loja D'Água';

SELECT nome, cidade
FROM fornecedores
WHERE nome = 'Loja D''Água';

SELECT LEFT(nome, 3) AS inicio
FROM clientes;

Referência rápida

Exercícios

Missão final: as etiquetas da papelaria

SELECT id, nome, preco
FROM produtos
WHERE categoria_id = 1;

SELECT 'P' || printf('%03d', id)                AS codigo,
       nome                                      AS produto,
       replace(printf('%.2f', preco), '.', ',')  AS preco_br
FROM produtos
WHERE categoria_id = 1;

Outra resposta certa para o código: printf('P%03d', id), com o P dentro do molde.

Resumo

• Funções de texto mudam o que o SELECT mostra, nunca o que está guardado.
• length conta caracteres (com acento, 1 cada); octet_length conta bytes.
• upper e lower no SQLite só trocam as letras A–Z: upper('Conceição') dá CONCEIçãO. Use-as sem medo em emails e códigos.
• substr(s, início, qtd) recorta, contando do 1; instr(s, t) acha a posição (0 se não acha). Juntas, separam primeiro nome, usuário e domínio do email.
• trim limpa as pontas; replace troca todas as ocorrências.
• || junta textos, mas um NULL derruba tudo; concat trata NULL como vazio e concat_ws pula o NULL junto com o separador. + não junta textos no SQLite.
• printf (ou format) carimba: '%.2f' para duas casas, '%05d' para zeros à esquerda. O resultado é texto, para mostrar.