Re: [Q] Removing all characters except some

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 05 Dec 2003 23:31:40 -0800
Message-ID: <1070695931.322547@yasure>

Martin Burbridge wrote:

> son42uk_at_yahoo.co.uk (Peter Amberg) wrote in message news:<f8232d10.0312030449.38c691aa_at_posting.google.com>...
>

>>I have a VARCHAR column in some table T. How can I zap away all
>>characters from the string except digits? I need this in a SELECT
>>clause, I need to order the entries by the integer values within the
>>string. There may be non-digit characters anywhere in the string, I
>>can't just use SUBSTR.
>>

>
>
> You can also use two translates in cases where you only want
> to specify the characters to keep.
>
> SQL> var s varchar2(100)
> SQL> exec :s := 'rubbish123in456here7_at_8*9!!'
>
> PL/SQL procedure successfully completed.
>
> SQL> select
> 2 translate(:s,'0'||translate(:s,'*0123456789','*'),'0')
> 3 from dual
> 4 /
>
> TRANSLATE(:S,'0'||TRANSLATE(:S,'*0123456789','*'),'0')
> -------------------------------------------------------------
>
> 123456789
>
> SQL> exec :s := ' 1 xx 2 3 p 1'
>
> PL/SQL procedure successfully completed.
>
> SQL> /
>
> TRANSLATE(:S,'0'||TRANSLATE(:S,'*0123456789','*'),'0')
> -------------------------------------------------------------
>
> 1231
>

Here's another solution:

CREATE TABLE test (
address VARCHAR2(40));

INSERT INTO test VALUES ('123 Main Street');
INSERT INTO test VALUES ('100 State Street');
INSERT INTO test VALUES ('1100 Broadway Street');
INSERT INTO test VALUES ('One Stromlof Blvd');

COMMIT; COL sortcol FORMAT 99999

SELECT address, TO_NUMBER(TRIM(TRANSLATE(address, '0ABCDEFGHIJKLMNOPOQRSTUVWXYZabcdefghijklmnopqrstuvwxyz.,','0'))) SORTCOL FROM test
ORDER BY SORTCOL;

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Sat Dec 06 2003 - 01:31:40 CST