Contagem de uma sequência em uma string

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
wkinoue
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 2
Registrado em: Sex, 13 Dez 2013 9:38 am

Olá pessoal, preciso de uma ajuda.

Possuo uma string que tem vários caracteres e números, às vezes separados por colchetes ([ ]), por chaves ({ }) e por pipes (|) entre eles, e preciso fazer uma contagem dos valores que estão dentro deles. Em determinadas circunstâncias, preciso que sejam combinadas.

Ex.:

Selecionar tudo

[LOG]2331|1321{VP}|4524|5512]|885|4242113234{eddx]|5424|4524
Nesse exemplo, poderia contar uma só sequência ou duas.
- quantas vezes a sequência 4524 aparecem;
- quantas vezes as sequências 4524 e 5512 aparecem.

Já tentei utilizando LENGTH, mas são aproximadamente 20 sequências diferentes, e consulta ficaria enorme utilizando o LENGTH.

Para contar apenas uma sequência, utilizei a função REGEXP_COUNT da seguinte forma:

Selecionar tudo

SELECT REGEXP_COUNT('campo','\|4524\|') FROM (SELECT REPLACE(REPLACE(REPLACE(REPLACE('campo','[','|'),']','|'),'{','|'),'}','|') campo FROM 'table').
O problema está em contabilizar quando são duas sequências diferentes:
Tentei utilizar a função REGEXP_COUNT da seguinte forma:

Selecionar tudo

SELECT REGEXP_COUNT('campo','\|4524\||\|5512\|') FROM 'table'
Mas ele conta ou 4524 o 5512.

Como faço para que a função REGEXP_COUNT faça a contagem quando são duas sequências diferentes, ou se existir alguma outra função.

Agradeço desde já pela ajuda.
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Olá wkinoue,

Bem vindo ao forum GLUFKE.

Para a solução do seu problema, vamos em partes:

Primeiro, teremos que remover da string tudo aquilo que não nos interessa, ou seja, todos os caracteres que não sejam "números" ou "pipe". Para isso, podemos usar a função "REGEXP_REPLACE" com o filtro "[^0-9\|]", onde só mantemos os digitos numericos e o pipe. Execute a querie abaixo:

Selecionar tudo

SQL> SELECT
'[LOG]2331|1321{VP}|4524|5512]|885|4242113234{eddx]|5424|4524' MINHA_STRING,
REGEXP_REPLACE('[LOG]2331|1321{VP}|4524|5512]|885|4242113234{eddx]|5424|4524', '[^0-9\|]','') MINHA_STRING_FILTRADA 
FROM DUAL

SQL>

MINHA_STRING                                                  MINHA_STRING_FILTRADA 
------------------------------------------------------------  ------------------------------------------
[LOG]2331|1321{VP}|4524|5512]|885|4242113234{eddx]|5424|4524  2331|1321|4524|5512|885|4242113234|5424|4524
Pronto ! Note que você tem uma linha somente com os valores numéricos, separados por pipe : 2331|1321|4524|5512|885|4242113234|5424|4524

Agora, vamos tentar identificar quantos valores existem na string. Você disse que o separador oficial seria o "pipe". Então, vamos repetir o comando REGEXP_REPLACE para identificar quantos pipes existem na string. Neste caso, vou usar o filtro "[^|]" para só manter o pipe da string inicial:

Selecionar tudo

SQL> SELECT REGEXP_REPLACE('2331|1321|4524|5512|885|4242113234|5424|4524', '[^|]','') TOTAL
     FROM DUAL;

TOTAL
-------
||||||| 
Mas fica muito chato ficar contando os "pauzinhos" resultandes da querie. Vamos melhorar a mesma com a ajuda dos comandos LENGTH e TRIM:

Selecionar tudo

SQL> SELECT LENGTH(TRIM(REGEXP_REPLACE('2331|1321|4524|5512|885|4242113234|5424|4524', '[^|]',''))) TOTAL
  2  FROM DUAL;

     TOTAL
----------
         7 
OK .. então até aqui já temos a "string purificada" e quantos valores numericos se encontram na string, com base na contagens dos separadores "pipe".

Para isso, precisamos do comando "REGEXP_SUBSTR ... CONNECT BY LEVEL ...". Execute agora esta querie

Selecionar tudo

SQL> SELECT trim(regexp_substr('2331|1321|4524|5512|885|4242113234|5424|4524', '[^|]+', 1, LEVEL)) RESULTADO
  2    FROM dual
  3  CONNECT BY LEVEL <= 7+1;

RESULTADO
--------------------------------------------------------------------------------
2331
1321
4524
5512
885
4242113234
5424
4524
Algumas explicações:

- Note no comando REGEXP_SUBTR que eu informei o segundo parâmetro como '[^|]+'. Isso indica para ele que este é o separador de valores.
- Note no LEVEL que eu adicionei 1 ao valor (ex: 7+1). O motivo é que os separadores sempre estão um número a menos do que o total dos registros.
- Note que eu usei LENGTH e REGEXP_REPLACE para identificar quantos separadores existem. No 10g este é a forma de se fazer isso. No 11g eu passo a usar a função REGEXP_COUNT

Bem .. agora vamos verificar qual a distribuicao destas valores na string. Vamos colocar a querie anterior dentro de um SELECT .. COUNT(*) FROM .. GROUP BY.

Execute agora:

Selecionar tudo

SQL> SELECT RESULTADO,COUNT(*)
  2  FROM
  3  (
  4  SELECT trim(regexp_substr('2331|1321|4524|5512|885|4242113234|5424|4524', '[^|]+', 1, LEVEL)) RESULTADO
  5    FROM dual
  6  CONNECT BY LEVEL <= 7+1
  7  )
  8  GROUP BY RESULTADO;

RESULTADO    COUNT(*)
---------- ----------
4524                2
1321                1
5424                1
5512                1
2331                1
885                 1
4242113234          1
Finalizamos então a contagem que você precisava fazer. Eu vou agora então repetir esta querie agregando nela tudo o que explicamos anteriormente:

Selecionar tudo

SQL> SELECT RESULTADO,COUNT(*)
  2  FROM
  3  (
  4  SELECT trim(regexp_substr(REGEXP_REPLACE('[LOG]2331|1321{VP}|4524|5512]|885|4242113234{eddx]|5424|4524', '[^0-9\|]',''), '[^|]+', 1, LEVEL)) RESULTADO
  5    FROM dual
  6  CONNECT BY LEVEL <= LENGTH(TRIM(REGEXP_REPLACE('[LOG]2331|1321{VP}|4524|5512]|885|4242113234{eddx]|5424|4524', '[^|]','')))+1
  7  )
  8  GROUP BY RESULTADO
  9  /

RESULTADO    COUNT(*)
---------- ----------
4524                2
1321                1
5424                1
5512                1
2331                1
885                 1
4242113234          1
Eu desenvolvi esta explicação por meio de consultas a sites na internet, que estou postando aqui:

http://www.sqlsnippets.com/en/topic-12818.html
http://stackoverflow.com/questions/1099 ... gits-again

Espero ter resolvido o seu problema, e agradeço por ter postado este assunto, uma vez que não tinha me deparado ainda com este tipo de problema.

Abraços,

Sergio Coutinho
wkinoue
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 2
Registrado em: Sex, 13 Dez 2013 9:38 am

Olá Sergio!

Muito obrigado pelas suas explicações. Com certeza vai me ajudar bastante em outras consultas que terei que fazer.

Entretanto, preciso de mais uma ajuda. Acho que não consegui explicar direito ou não consegui utilizar a sua explicação. Preciso contabilizar quantas vezes duas sequencias aparecerem. Por exemplo, nessa string:

Selecionar tudo

[LOG]|2331{VP}5354|3215|5331|982[eddx]|3443|5234|2331{VP}|4353|3215|903]|2599
      ^^^^         ^^^^                          ^^^^          ^^^^
Vamos supor que as sequencias fossem "2331 e 3215". Na string acima, elas apareceriam 2x.

Preciso contar quantas vezes a sequencia "2331 e 3215" aparecem na string.

Mais uma vez, muito obrigado!
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Acho que entendi ..

Vamos supor que [yyy] e {yyy} possam ser considerados separadores, com o mesmo poder do PIPE.

Execute então esta querie, baseada no exemplo que me passou:

Selecionar tudo

SELECT RESULTADO,COUNT(*)
 FROM
 (
 SELECT trim(regexp_substr(REGEXP_REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('[LOG]|2331{VP}5354|3215|5331|982[eddx]|3443|5234|2331{VP}|4353|3215|903]|2599','[','|'),']','|'),'{','|'),'}','|'),  '[^0-9\|]',''), '[^|]+', 1, LEVEL)) RESULTADO
   FROM dual
 CONNECT BY LEVEL <= LENGTH(TRIM(REGEXP_REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('[LOG]|2331{VP}5354|3215|5331|982[eddx]|3443|5234|2331{VP}|4353|3215|903]|2599','[','|'),']','|'),'{','|'),'}','|'),'[^|]','')))+1
 )
 GROUP BY RESULTADO
/

RESULTA   COUNT(*)
------- ----------
3443             1
                 9
3215             2
2331             2
982              1
5354             1
5331             1
2599             1
5234             1
903              1
4353             1

Não foi uma solução muuuuito bonita .. eu tive que fazer algums REPLACES para converter os "[","]","{","}" em caracteres "|".

Do resultado da querie acima, você pode desconsiderar as 9 ocorrencias de NULL.

Espero que esta adaptação resolva o seu problema.

Att

Sergio Coutinho
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 11 visitantes