I have the customer base, and the customer base:
COD CLIENTE
128 JOAO
129 MARIA
130 DULCE
NOTA
234.123.334.128.50
235.124.338.128.49
Note that the customer code is inserted in the note number, which in this case is the penultimate set before the last point (128).
How do I "blow up" these 3 code numbers in the notes table to relate to the code registered in my customer base?
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:
Pass the string, the position you want, and the delimiter to the function.
select gettoken( '234.123.334.128.50', 4, '.' ) from dual