Character Explosion in Oracle

Question

I have the client base, and the client base:

   COD  CLIENTE
    128  JOAO
    129  MARIA
    130  DULCE


    NOTA
    234.123.334.128.50
    235.124.338.128.49

Note that the client code is inserted in the note number, which in this case is the penultimate set before the last point (128).

How do I "explode" these 3 code numbers in the note table to relate to the code registered in my client base?

Answer 1

Create a function in Oracle:

create or replace function GetToken( 
   tokenstring in varchar,
   tokenpos in number,
   delim in varchar )
   return varchar
is
   x number;
   i number;
   j number;
begin
  x := 1;
  i := 0;
  j := 0;
  while (i < TokenPos)
  loop
    j := x;
    while (x <= length( TokenString )) and ( substr( TokenString, x, 1 ) <> Delim) 
    loop
       x := x + 1;
    end loop;
    i := i + 1;
    x := x + 1;
  end loop;
  return trim(substr(TokenString, j, x - j - 1));
end;

Now you select the parts as you want so:

Switch to the string function, the position you want and the delimiter.

select gettoken( '234.123.334.128.50', 4, '.' ) from dual