Trimming multiple values from a field?

[dsquare]

I have searched through the FAQ and did not find an answer to my question/problem. I'm am working on DBC Release V2R.04.01.01.01, DBS Version 04.01.01.06. I am trying to strip out the leading numbers that are found in and address field. For example, 123 MAIN ST translates to MAIN ST. The leading numbers could be seperated from the actual street name by any alpha character (including Unicode). I know that I can do this with a large CASE statement or using Volatile tables or stored procedures. However, stored procedures are not very stable in this release. Volatile tables would mean multiple passes. The CASE statement is some what limiting for this specific purpose.
Is there any other way to accomplish this task?
It would be nice if the TRIM function could somehow accept multiple values. (eg. TRIM(address IN ('1','2','3','4')))

Thanks,
DSquare

 

[BillDHS]

Maybe I can give you a hunt in the right direction: use index to find the space character and sustring to extract the rest of the field. You may want to add a trim to reduce blank characters.

(substr(db.address,index(db.address, ' '), 50))

The 50 is the max field size.

 

[dsquare]

BillDHS,
This would work if a space is always after the street number. However, there are international addresses that use various characters immediately after the street number (EG. 123,main). Thus, the potential is that any non-numeric character could follow the street number. I am currently using a CASE statement and I'm looking at the first 7 positions in the data value. The problem is if there is an address with an 8 digit street number my logic would not capture the 8th digit.
I don't want this to become a maintenance nightmare everytime there is a larger street number.

DSquare