Blog do Marcio Portes

Outro dia respondi uma dúvida em um forum e achei interessante para compartilhar.

A dúvida

Gostaria de saber com criar uma funcao que faca o seguinte:
separe um codigo em prefixo, medida e sulfixo.
os codigos podem ter o seginte formato

XXX9999YYY
XX9999999YY
XXXX99

sedo que:

X é o prefixo e eh sempre um conjunto de caracteres
9 é a medida e eh sempre numerico
Y é o sulfixo, sempre caracteres, podendo existir ou nao.

o tamanho de cada cadeia de caracteres pode variar, ex:

XXXX; XXX; XX; XXXXX
999; 9999; 99999
YY; YYY; YYYYYY ou sem caracter.

Primeiro, vou reproduzir exatamente o exemplo dado.

ops$marcio:YODA10G> create table t ( x varchar2(15) );

Table created.

ops$marcio:YODA10G> insert into t values ( 'XXX9999YYY' );

1 row created.

ops$marcio:YODA10G> insert into t values ( 'XX9999999YY' );

1 row created.

ops$marcio:YODA10G> insert into t values ( 'XXXX99' );

1 row created.

Abaixo, a técnica: Vejo se consigo encontrar a posição onde começa e termina os numerais. Portanto:

XXX9999YYY
1234567890  = Incio,fim (4,7)

XX9999999YY
12345678901 = Inicio,fim (3,9)

Por que necessito disso? Porque eu poderei separar facilmente os valores usando a função SUBSTR mais a frente. A substr pede inicio e quantidade de bytes a separar, com o inicio e fim dos numeros na string, basta subtrair que terei o tamanho dos numeros.
Ex.

XXX9999YYY
1234567890  = Incio,fim (4,7)

tam=fim-inicio = tam=7-4; tam=3 (+1 da posição inicial).

    A substr, começanda a posição 4, extraindo 
    4 bytes são exatamente os numeros que estão 
    compreendidos na string.

XXX9999YYY
   +--+
1234 ----------= *posição inicial do SUBSTR + 4 bytes (9999).

Demonstrando:
ops$marcio:YODA10G>
ops$marcio:YODA10G> select x,
  2         instr(n, '9' ) prm,
  3         instr(n, '9', -1) ult
  4    from (
  5  select translate(x,'1234567890','9999999999') n, x
  6    from t
  7  )
  8  /

X                         PRM           ULT
--------------- ------------- -------------
XXX9999YYY                  4             7
XX9999999YY                 3             9
XXXX99                      5             6

3 rows selected.

ops$marcio:YODA10G>
ops$marcio:YODA10G> select x
  2       , substr(x, 1, instr(n,'9')-1 ) prefixo
  3       , substr(x, instr(n,'9'), (instr(n,'9', -1)+1) - instr(n,'9')) medida
  4       , substr(x, instr(n,'9', -1)+1 ) sulfixo
  5    from ( select x, translate(x,'1234567890','9999999999') n from t )
  6  /

X               PREFIXO         MEDIDA          SULFIXO
--------------- --------------- --------------- ---------------
XXX9999YYY      XXX             9999            YYY
XX9999999YY     XX              9999999         YY
XXXX99          XXXX            99

3 rows selected.

Agora incrementando o teste para que a query acima seja verdade.

ops$marcio:YODA10G> insert into t values ( 'ab1234567vc');

1 row created.

ops$marcio:YODA10G> insert into t values ( 'mn0z');

1 row created.

ops$marcio:YODA10G> insert into t values ( 'ooo123');

1 row created.

ops$marcio:YODA10G> select x
  2       , substr(x, 1, instr(n,'9')-1 ) prefixo
  3       , substr(x, instr(n,'9'), (instr(n,'9', -1)+1) - instr(n,'9')) medida
  4       , substr(x, instr(n,'9', -1)+1 ) sulfixo
  5    from ( select x, translate(x,'1234567890','9999999999') n from t )
  6  /

X               PREFIXO         MEDIDA          SULFIXO
--------------- --------------- --------------- ---------------
XXX9999YYY      XXX             9999            YYY
XX9999999YY     XX              9999999         YY
XXXX99          XXXX            99
ab1234567vc     ab              1234567         vc
mn0z            mn              0               z
ooo123          ooo             123

6 rows selected.

ops$marcio:YODA10G>

Labels: how to