Dividindo Strings com MySQL

Às vezes, quando você está escrevendo consultas SQL, pode ser necessário dividir uma string em um determinado delimitador. Por exemplo, se você deseja dividir um endereço de várias linhas em colunas individuais. Se você estivesse fazendo isso em PHP, seria muito fácil. Você simplesmente reúne os resultados retornados e usa explode para dividir a string. Infelizmente, não existe uma função de “divisão” que faça isso no MySQL, mas pode ser obtida com o uso inteligente de uma função de string diferente.

Vamos continuar com o exemplo de um endereço multilinha. Aqui está nossa tabela de endereços:

CREATE TABLE `address` (
    `id` INTEGER AUTO_INCREMENT PRIMARY KEY,
    `fullname` VARCHAR(255),
    `company` VARCHAR(255),
    `street` VARCHAR(255),
    `city` VARCHAR(255),
    `state` VARCHAR(255),
    `zip` VARCHAR(20),
    `country` VARCHAR(255)
);

E aqui estão os dados inseridos:

INSERT INTO `address` VALUES (
    NULL,
    'Test Testerson',
    'ACME, Inc',
    '123 Acme WaynSuite 200',
    'San Francisco',
    'California',
    '94114',
    'United States'
);

Digamos que desejamos consultar essa tabela e dividir as informações da rua em várias colunas, de modo que cada linha seja sua própria coluna. Podemos fazer isso usando a função SUBSTRING_INDEX no MySQL.

SELECT SUBSTRING_INDEX(street, 'n', 1) AS street1, SUBSTRING_INDEX(street, 'n', 2) AS street2, SUBSTRING_INDEX(street, 'n', 3) AS street3 FROM address;

Mas, isso ainda não funciona. Aqui está o que é retornado:

street1         street2                    street3
123 Acme Way    123 Acme Way↵Suite 200    123 Acme Way↵Suite 200

Funciona para isolar street1, mas street2 e street3 estão errados. Se você ler a documentação do MySQL com atenção, notará que SUBSTRING_INDEX retorna tudo à esquerda do delimitador. Queremos a string entre a primeira e a segunda ocorrência do delimitador, o que podemos realizar envolvendo street2 e street3 em outra chamada SUBSTRING_INDEX. Agora podemos dizer a ele para recuperar tudo à direita da última ocorrência do delimitador. Nossa consulta agora se parece com isto:

SELECT
    SUBSTRING_INDEX(street, 'n', 1) AS street1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(street, 'n', 2), 'n', -1) AS street2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(street, 'n', 3), 'n', -1) AS street3
FROM address;

Estamos quase lá, mas não exatamente. Você pode ver que street3 está retornando o mesmo valor que street2:

street1         street2         street3
123 Acme Way    Suite 200       Suite 200

Ele está fazendo isso porque há apenas duas filas em nossa rua. Para contabilizar isso, precisamos contar o número de linhas e, em seguida, usar instruções IF para controlar nosso SUBSTRING_INDEX:

SELECT
    @num_street_lines := 1 + LENGTH(street) - LENGTH(REPLACE(street, 'n', '')) AS num_street_lines,
    SUBSTRING_INDEX(street, 'n', 1) AS street1,
    IF(@num_street_lines > 1, SUBSTRING_INDEX(SUBSTRING_INDEX(street, 'n', 2), 'n', -1), '') AS street2,
    IF(@num_street_lines > 2, SUBSTRING_INDEX(SUBSTRING_INDEX(street, 'n', 3), 'n', -1), '') AS street3
FROM address;

Embora nossa consulta agora seja longa e difícil de ler, ela funciona. E para aquelas situações em que você deve fazer tudo em SQL e não pode usar uma linguagem como o PHP para processar sua consulta, isso é muito útil.