eliminate non numeric portion 3

[DotNetGnat]

Guys,

I have a field in the database that has house numbers. In one of my queries I do some computations using this field and occasionally this field has some characters besides numbers. When I do computations i am getting an "invalid number" error.

So i want to eliminate the non-numeric portion or characters from this field.

Any suggestions.

Thanks

-DNG

 

[Beilstwh]

Do you want to skip over those rows that contain non numeric fields or do you want to convert something like "399fkj34ss9" to "399349" (stripping out characters)

Bill
Oracle DBA/Developer
New York State, USA

 

[DotNetGnat]

mostly the house numbers are in the following format

550A
550 Apt#B
550C
550AB

I just want the numeric portion or the numeric house number.

Thanks

-DNG

 

[SantaMufasa]

DNG,

Here is a function that strips away all characters from an incoming string except for numerals:

create or replace function strip_to_num_only (str_in varchar2) return varchar2 is
    non_num varchar2(256);
begin
    for i in 1..256 loop
        if i between 48 and 57 then null; else
            non_num := non_num||chr(i);
        end if;
    end loop;
    return translate(str_in,chr(48)||non_num,chr(48));
end;
/

Function created.

Here is a sample execution:

select strip_to_num_only('abd123efg456') Num_only from dual;

NUM_ONLY
--------
123456

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[taupirho]

Try doing a double translate on your input string, something like :-


1 select
2 translate('550 Apt#B','0'||translate('550 Apt#B','*0123456789','*'),'0')
3* from dual
SQL> /

TRA
---
550



In order to understand recursion, you must first understand recursion.

 

[DotNetGnat]

Awesome guys,

Thanks so much. I appreciate it. Have stars.

-DNG