Funções de string no PostgreSQL

1. Introdução às Funções de String no PostgreSQL

O processamento de strings é uma das tarefas mais comuns em consultas SQL, seja para limpeza de dados, formatação de relatórios ou validação de entradas. O PostgreSQL se destaca nesse aspecto por oferecer um conjunto robusto e flexível de funções de string, muitas vezes superior ao de outros SGBDs como MySQL ou SQL Server.

Enquanto sistemas como o Oracle exigem a tabela DUAL para consultas sem tabela real, o PostgreSQL permite executar funções diretamente com SELECT simples. Além disso, o suporte nativo a expressões regulares e funções de agregação como STRING_AGG tornam o PostgreSQL uma escolha poderosa para manipulação textual.

As funções de string no PostgreSQL podem ser categorizadas em:
- Funções de manipulação básica (concatenação, formatação)
- Funções de extração e busca
- Funções de substituição e limpeza
- Funções de formatação e padding
- Expressões regulares
- Funções avançadas (hash, codificação, agregação)

2. Funções Básicas de Manipulação de Strings

Concatenação

SELECT CONCAT('SQL', ' ', 'PostgreSQL');
-- Resultado: SQL PostgreSQL

SELECT CONCAT_WS(', ', 'João', 'Maria', 'Pedro');
-- Resultado: João, Maria, Pedro

SELECT 'Curso' || ' ' || 'Completo';
-- Resultado: Curso Completo

CONCAT_WS (concat with separator) é especialmente útil para unir strings com um separador comum, ignorando valores NULL automaticamente.

Formatação de Caixa

SELECT UPPER('postgresql');
-- Resultado: POSTGRESQL

SELECT LOWER('POSTGRESQL');
-- Resultado: postgresql

SELECT INITCAP('banco de dados relacional');
-- Resultado: Banco De Dados Relacional

INITCAP capitaliza a primeira letra de cada palavra, sendo útil para nomes próprios e títulos.

Medição de Strings

SELECT LENGTH('PostgreSQL');
-- Resultado: 10

SELECT CHAR_LENGTH('café');
-- Resultado: 4 (caracteres reais)

SELECT OCTET_LENGTH('café');
-- Resultado: 5 (bytes, pois 'é' ocupa 2 bytes em UTF-8)

A diferença entre CHAR_LENGTH e OCTET_LENGTH é crucial ao trabalhar com textos multibyte como acentos e emojis.

3. Extração e Busca em Strings

SUBSTRING e SUBSTR

SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 6);
-- Resultado: Postgr

SELECT SUBSTR('PostgreSQL', 5, 4);
-- Resultado: grES

SUBSTRING permite sintaxe SQL padrão com FROM e FOR, enquanto SUBSTR é uma variação mais compacta.

LEFT e RIGHT

SELECT LEFT('0123456789', 3);
-- Resultado: 012

SELECT RIGHT('0123456789', 4);
-- Resultado: 6789

POSITION, STRPOS e SPLIT_PART

SELECT POSITION('SQL' IN 'PostgreSQL');
-- Resultado: 7

SELECT STRPOS('banana', 'na');
-- Resultado: 3

SELECT SPLIT_PART('2024-03-15', '-', 2);
-- Resultado: 03

SPLIT_PART é extremamente útil para extrair partes de strings delimitadas, como datas ou caminhos de arquivos.

4. Substituição e Limpeza de Strings

REPLACE

SELECT REPLACE('João Silva', 'Silva', 'Pereira');
-- Resultado: João Pereira

TRANSLATE

SELECT TRANSLATE('123-456-789', '123', 'ABC');
-- Resultado: ABC-456-789

TRANSLATE substitui caracteres individualmente, diferente de REPLACE que substitui strings completas.

TRIM, LTRIM, RTRIM e BTRIM

SELECT TRIM('  PostgreSQL  ');
-- Resultado: PostgreSQL

SELECT LTRIM('  PostgreSQL');
-- Resultado: PostgreSQL

SELECT RTRIM('PostgreSQL  ');
-- Resultado: PostgreSQL

SELECT BTRIM('xxPostgreSQLxx', 'x');
-- Resultado: PostgreSQL

BTRIM aceita um segundo parâmetro para remover caracteres específicos das bordas, não apenas espaços.

5. Formatação e Padding de Strings

LPAD e RPAD

SELECT LPAD('7', 5, '0');
-- Resultado: 00007

SELECT RPAD('Nome', 10, '.');
-- Resultado: Nome......

Útil para gerar códigos padronizados ou alinhar colunas em relatórios.

REPEAT

SELECT REPEAT('*', 5);
-- Resultado: *****

FORMAT

SELECT FORMAT('Olá %s, você tem %s anos.', 'Maria', 30);
-- Resultado: Olá Maria, você tem 30 anos.

Similar ao printf do C, FORMAT permite formatação avançada com placeholders %s (string), %d (inteiro), %f (float).

6. Expressões Regulares no PostgreSQL

Operadores de Correspondência

SELECT 'PostgreSQL' ~ '^Post';
-- Resultado: true

SELECT 'PostgreSQL' ~* '^post';
-- Resultado: true (case insensitive)

SELECT 'PostgreSQL' !~ '^SQL';
-- Resultado: true (negação)

Funções REGEXP

SELECT REGEXP_MATCHES('email@exemplo.com', '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');
-- Resultado: {email@exemplo.com}

SELECT REGEXP_SPLIT_TO_TABLE('um|dois|tres', '\|');
-- Resultado: 
-- um
-- dois
-- tres

SELECT REGEXP_REPLACE('Telefone: (11) 99999-8888', '[^0-9]', '', 'g');
-- Resultado: 11999998888

O flag 'g' (global) em REGEXP_REPLACE substitui todas as ocorrências, não apenas a primeira.

Exemplo Prático: Validação de CPF

SELECT '529.982.247-25' ~ '^\d{3}\.\d{3}\.\d{3}-\d{2}$';
-- Resultado: true

7. Funções Avançadas e Codificação

ASCII e CHR

SELECT ASCII('A');
-- Resultado: 65

SELECT CHR(65);
-- Resultado: A

ENCODE e DECODE

SELECT ENCODE('PostgreSQL', 'base64');
-- Resultado: UG9zdGdyZVNRTDk=

SELECT DECODE('UG9zdGdyZVNRTDk=', 'base64');
-- Resultado: PostgreSQL

Funções de Hash

SELECT MD5('senha123');
-- Resultado: e10adc3949ba59abbe56e057f20f883e

SELECT SHA256('mensagem');
-- Resultado: f3e5e7f8b8a9c0d1e2f3a4b5c6d7e8f9...

STRING_AGG para Agregação

SELECT departamento, STRING_AGG(nome, ', ' ORDER BY nome) AS funcionarios
FROM empregados
GROUP BY departamento;

STRING_AGG é o equivalente ao GROUP_CONCAT do MySQL, permitindo concatenar valores de múltiplas linhas em uma única string.

8. Boas Práticas e Performance

Índices e Operadores LIKE/ILIKE

Para buscas com LIKE, utilize índices com o operador varchar_pattern_ops:

CREATE INDEX idx_nome ON clientes (nome varchar_pattern_ops);
SELECT * FROM clientes WHERE nome LIKE 'João%';

Para buscas case-insensitive, ILIKE é mais eficiente que UPPER(coluna) LIKE UPPER('valor').

Uso de COLLATE

SELECT nome FROM clientes ORDER BY nome COLLATE "pt_BR";

O COLLATE permite ordenação específica para o português brasileiro, tratando corretamente acentos e caracteres como 'ç'.

Cuidados com Encoding

Sempre verifique o encoding do banco com:

SHOW server_encoding;
-- Resultado: UTF8

Ao usar funções como OCTET_LENGTH, lembre-se que caracteres acentuados e emojis ocupam mais bytes. Prefira CHAR_LENGTH para contar caracteres reais.

Referências