Início / dba / Perguntas / 313122
Accepted
gabtzi
Asked: 2022-06-09 13:23:25 +0800 CST

Extraia uma substring onde o delimitador pode aparecer mais de uma vez

  • 772

Eu tenho uma coluna de strings com esse padrão <email> - <id>. Email é sempre a primeira string.

Eu gostaria de extrair apenas o endereço de e-mail, mas o problema aqui é que um endereço de e-mail também pode conter hífens, então não posso ter certeza de que o delimitador ocorrerá apenas uma vez.

Então, basicamente, eu gostaria de combinar .* até o último hífen e extrair isso como e-mail.

Bem, não se trata exatamente de administração, trata-se de escrever uma consulta para extrair dados, então está na área de mineração de dados, no entanto, este fórum está relacionado inteiramente ao banco de dados, então acho mais apropriado que o stackoverflow.

Eu só tentei SUBSTRING_INDEX(), mas acabei obtendo resultados ruins com ele.

É um sistema de produção, então não posso interferir no design, daí a necessidade de extrair as informações.

mariadb mariadb-10.4

3 respostas

  1. Best Answer

    Existem algumas possibilidades aqui - Solution1 usa funções de string padrão do MariaDB , e Solution2 faz uso de regular expressions( regexes-excelente site aqui , início rápido aqui ). Você também pode usar GENERATEDcolunas para facilitar sua vida.

    Solução 1 (usando funções de string MySQL/MariaDB comuns):

    Se você tem certeza de que seus dados estão limpos e todos os campos começam com <+ email + > - <.... more stuff..., você pode fazer o seguinte (todo o código da Solução 1 pode ser encontrado no violino aqui ):

    CREATE TABLE test_ter
(
  field VARCHAR (200) NOT NULL
);

    dados:

    INSERT INTO test_ter VALUES
('<[email protected]> - <1345>'),
('<[email protected]> - <1345>'),
('<rubbish> - <[email protected]> - <1345>'),
('<more_rubbish> - <[email protected]> - <1345>'),
('<more stuff> - <[email protected] - <34343>');

    Em seguida, executamos:

    SELECT
  field, 
  INSTR(field, '> - <') AS instr, 
  POSITION('> - <' IN field) AS pos, 
  LOCATE('> - <', field) AS loc,
  SUBSTRING_INDEX(field, '> - <', 1) AS substr
FROM
  test_ter;

    Resultado:

    field                                       instr pos loc   substr
<[email protected]> - <1345>                        14  14   14  <[email protected]
<[email protected]> - <1345>                        14  14   14  <[email protected]
<rubbish> - <[email protected]> - <1345>             9   9    9  <rubbish
<more_rubbish> - <[email protected]> - <1345>   14  14   14  <more_rubbish
<more stuff> - <[email protected] - <34343>  12  12   12  <more stuff

    Podemos ver que SUBSTRING_INDEX()nos aproxima da resposta que queremos - caso contrário, teremos que usar mais funções aninhadas para obter o resultado desejado - veja as edições anteriores desta resposta.

    Combinamos SUBSTING_INDEX()com a TRIM()função para obter nossa resposta:

    SELECT 
  TRIM(LEADING '<' FROM SUBSTRING_INDEX(field, '> - <', 1))
FROM                                                                  
  test_ter;

    Resultado:

    TRIM(LEADING '<' FROM SUBSTRING_INDEX(field, '> - <', 1))
[email protected]
[email protected]
rubbish
more_rubbish
more stuff

    Dependendo de quão limpos são seus dados de entrada (suponho que alguns e-mails inválidos - a verificação mais básica é que a string contém um @sinal.

    Podemos combinar isso com o uso de GENERATEDcolunas, você pode fazer um pouco de verificação antes que qualquer bit atinja o disco da seguinte maneira:

    ALTER TABLE test_ter
ADD COLUMN email VARCHAR (200)
GENERATED ALWAYS AS
(
  CASE
    WHEN 
      INSTR(TRIM(LEADING '<' FROM SUBSTRING_INDEX(field, '> - <', 1)), '@') = 0 
        THEN NULL
    ELSE
      TRIM(LEADING '<' FROM SUBSTRING_INDEX(field, '> - <', 1))
  END
);

    e verificar: SELECT * FROM test_ter;- Resultado:

    field                                          email
<[email protected]> - <1345>                        [email protected]
<[email protected]> - <1345>                        [email protected]
<rubbish> - <[email protected]> - <1345>            NULL
<more_rubbish> - <[email protected]> - <1345>   NULL
<more stuff> - <[email protected] - <34343>  NULL

    Assim, podemos ver que os registros que não contêm um e-mail no primeiro < >par são considerados NULL- mas se você estiver feliz que suas entradas estejam limpas, isso é desnecessário.

    Você também pode usar um índice em seu GENERATEDcampo para acelerar as pesquisas, se for apropriado:

    CREATE INDEX tt_email_ix ON test_ter (email);

    Outra resposta levantou a possibilidade de que o <e o >eram apenas espaços reservados e que seus dados estão no formulário [email protected] - stuff...., então tudo o que você precisa é algo como

    ...
SUBSTRING_INDEX(field, ' ', 1) -- 1 space, or use 1 space and a hyphen ' -`
...

    Isso truncará a string deixando apenas o email (veja o violino).

    Solução 2 (usando regexes):

    Você pode fazer o seguinte (todo o código da Solução 2 pode ser encontrado no violino aqui ):

    CREATE TABLE test
(
  field VARCHAR (200) NOT NULL
);

    Preencha com alguns dados de exemplo:

    INSERT INTO test VALUES
('<[email protected]> - <1345>'),
('<[email protected]> - <1345>'),
('<rubbish> - <[email protected]> - <1345>'),
('<more_rubbish> - <[email protected]> - <1345>');

    e, em seguida, execute (usando um regular expression- regex):

    SELECT 
  REGEXP_SUBSTR
  (
    field, 
    '[A-Z][A-Z0-9._-]+@[A-Z0-9_-]+\.[A-Z]{2,4}'
  ) AS email
FROM 
  test;

    Resultado:

    email
[email protected]
[email protected]
[email protected]
[email protected]

    Agora, o regex simples que usei para um e-mail é [A-Z][A-Z0-9._-]+@[A-Z0-9_-]+\.[A-Z]{2,4}- você pode torná-lo tão complexo quanto desejar / exigir - veja aqui - uma solução regex vinculada tem 6.500 caracteres, talvez um exagero? Uma pesquisa lhe dará o compromisso entre a solução ser robusta e ser adequada para você.

    Regex explicado (um excelente site sobre regexes pode ser encontrado aqui , início rápido aqui ):

    • [A-Z]

      deve começar com uma única letra - ou seja, A-Zou a-z. Não é muito correto de acordo com aqui - mas esta é apenas uma simples primeira aproximação. No MySQL/MariaDB, apenas [A-Z]funcionará com os agrupamentos que não diferenciam maiúsculas de minúsculas que são o padrão.

    • [A-Z0-9._-]+

      o restante do e-mail antes do @sinal - corresponda aos caracteres A-Zou a-zuma ._-ou mais vezes (o +"metacaractere" especifica isso - consulte o início rápido - os metacaracteres têm um significado especial em regexes),

      os colchetes [e ]coloque o que é chamado de classes de caracteres ou conjuntos de caracteres - veja o link de início rápido acima,

    • @corresponder ao sinal literal "arroba",

    • mais letras, dígitos e _-para o nome do site,

    • \.corresponder ao ponto literal (ponto final ou ponto - ou seja ., caractere). O .é escapado com a barra invertida ( \), pois o ponto também é um metacaractere - sem escape ele representa qualquer caractere único - como sublinhado ( _) em SQL,

    • [A-Z]{2,4}o nome de domínio - corresponde às letras [AZ] (e [az]) ocorrendo 2, 3 ou 4 vezes - ou seja .fr, .comou infopor exemplo.

      As chaves ( {, }) servem para especificar o número de repetições. Se você tivesse apenas {3}, isso significaria 3 e três apenas ocorrências do seu padrão desejado.

    Esteja ciente de que os regexes são caros e, dependendo do tamanho da sua tabela e do comprimento de suas strings, suas consultas podem ser lentas. Você pode reduzir o custo do tempo de consulta às custas de um pouco de espaço em disco usando GENERATED COLUMNs da seguinte forma:

    CREATE TABLE test_bis
(
  field VARCHAR (200) NOT NULL,
  email VARCHAR (200) AS
  (
    REGEXP_SUBSTR
    (
      field, 
      '[A-Z][A-Z0-9._-]+@[A-Z0-9._-]+\.[A-Z]{2,4}'
    )
  ) PERSISTENT -- HDD cost,  also works with VIRTUAL - CPU cost.
);

    Fiz o mesmo INSERT- veja violino e o resultado é:

    field                                         email
<[email protected]> - <1345>                       [email protected]
<[email protected]> - <1345>                       [email protected]
<rubbish> - <[email protected]> - <1345>           [email protected]
<more_rubbish> - <[email protected]> - <1345>  [email protected]

    Você pode indexar este PERSISTENTcampo para acelerar a pesquisa:

    CREATE INDEX fb_regex_email
ON test_bis (email);

    Até onde eu sei, o MariaDB ainda não possui índices funcionais (ou de expressão) (veja PostgreSQL por exemplo).

    Se você não deseja sacrificar o espaço do disco rígido, você pode fazer a GENERATEDcoluna VIRTUALem vez disso - ao custo de ciclos de CPU - à vous le choix! Eu não posso testar o índice porque a tabela de amostra é tão pequena que o MySQL apenas faz uma varredura de tabela de qualquer maneira, índice presente ou não.

    Eu apenas sugiro que você teste essas soluções com seu próprio hardware e seus próprios dados apenas para ter certeza de que seu desempenho é ideal para seus requisitos/restrições.

    • 2

  2. Faça de trás para frente: pesquise a substring até o hífen do final da string (usando a SUBSTRING_INDEXfunção) e depois TRIMa parte encontrada do valor.

    Se o delimitador for estritamente como mostrado ( -hífen com espaço antes e depois), use-o como o delimitador de três caracteres. - Akina

    Exemplo

    CREATE TABLE test
(
    field varchar (200) NOT NULL
);

INSERT INTO test VALUES
('[email protected] - 1234'),
('[email protected] - 5678'); 

SELECT
    TRIM(TRAILING 
        CONCAT(' - ', 
            SUBSTRING_INDEX(field, ' - ', -1))
        FROM field) AS email
FROM test;
    o email
    [email protected]
    [email protected]

    db<> demonstração de violino

    • 1

  3. Eu tenho uma coluna de strings com esse padrão -

    E aí está o seu maior problema.
    Você tem dois bits de dados em um campo e isso é fundamentalmente uma má ideia.

    A primeira pergunta que você deve fazer antes de decidir como armazenar quaisquer Dados é

    Como vou acessar esses Dados?

    Você realmente deve ter isso em dois campos separados e, em seguida, esse problema de "extração" simplesmente "desaparece".

    Bancos de dados são muito, muito bons em encontrar pequenos pedaços de dados e juntá-los.
    Eles geralmente são muito ruins em encontrar grandes pedaços de dados e separá-los.

    • -1

relate perguntas