function replace together with wildcards ? [message #371181] |
Wed, 13 September 2000 06:33 |
claudia
Messages: 3 Registered: September 2000
|
Junior Member |
|
|
Hey, my problem is to erase 'A0', 'B0', 'C0', .. 'Z0' (= any character directly followed with zero) from a character string I select from a table without erasing valid data, for example:
F10C0M0 should be: F10
F0X0U0 should be: null
A5B0C12D4 should be: A5C12D4
F8M100 is valid
Is it possible to use function replace together with a wildcard for char(1) to erase these char||zero values like: replace(string,'a0') ??
Thanks in advance, Claudia
|
|
|
Re: function replace together with wildcards ? [message #371184 is a reply to message #371181] |
Wed, 13 September 2000 08:09 |
Mahesh Pednekar
Messages: 28 Registered: August 2000
|
Junior Member |
|
|
Create the following function :-
create or replace function myreplace(instring varchar2)
return varchar2 is
temp_string varchar2(100);
out_string varchar2(100);
ch varchar2(2);
begin
temp_string := instring;
for I in 65..91
loop
ch := chr(I)||'0';
select replace(upper(temp_string),upper(ch),'') into out_string from dual;
temp_string := out_string;
end loop;
Once the function is created you can use it for your purpose. e.g :-
select myreplace('F10C0M0') from dual;
or
select myreplace(fieldname) from table;
If you have any problem mail me at bunty609@hotmail.com.
|
|
|