Re: Remove numbers from string

2002-07-02 Thread Alexandre Gorbatchev
Jared's passion to regular expessions is understandable after book Perl for Oracle DBAs :) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 02, 2002 8:03 AM Yes, but not nearly as flexible as owa_pattern, and infinitely more

RE: Remove numbers from string

2002-07-02 Thread Grabowy, Chris
Sigh. There are way too many packages in Oracle. Thanks Jared. I have to share this with a developer struggling to write some parsing functions. I'll take back all the bad reviews I posted on bookpool.com about your book... -Original Message- Sent: Tuesday, July 02, 2002 12:57 AM To:

RE: Remove numbers from string

2002-07-02 Thread Jared . Still
: Remove numbers from string Sigh. There are way too many packages in Oracle. Thanks Jared. I have to share this with a developer struggling to write some parsing functions. I'll take back all the bad reviews I posted on bookpool.com about your book... -Original Message- Sent

Re: Remove numbers from string

2002-07-01 Thread Stephane Faroult
Nils Höglund wrote: Is there any way to remove all numbers from a string? The string might look like this: 'abc123def432' and I wan't it to look like this: 'abcdef'. I'm using Oracle 8. Thanks in advance! -- /Nils Höglund, Naqua KB E-mail: [EMAIL PROTECTED] Web:

Re: Remove numbers from string

2002-07-01 Thread Jan Pruner
Use TRANSLATE function (http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/function.htm#84984) SELECT TRANSLATE('abc123def432' ,'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' ,'ABCDEFGHIJKLMNOPQRSTUVWXYZ') FROM DUAL; JP On Monday 01 July 2002 13:18, you wrote: Is

Re: Remove numbers from string

2002-07-01 Thread Nils Höglund
You may have to use an intermediate stage, first replacing all numbers by say '#', then replacing '#' by ''. This is something I would like to avoid, since my string-fields may contain any characters. Any ideas? -- /Nils Höglund, Naqua KB E-mail: [EMAIL PROTECTED] Web:

Re: Remove numbers from string

2002-07-01 Thread Jan Pruner
On Monday 01 July 2002 14:09, Nils Höglund wrote: You may have to use an intermediate stage, first replacing all numbers by say '#', then replacing '#' by ''. This is something I would like to avoid, since my string-fields may contain any characters. Any ideas? SELECT

Re: Remove numbers from string

2002-07-01 Thread Tim Gorman
Instead of # character, replace with an unprintable character such as CHR(1)? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, July 01, 2002 6:43 AM On Monday 01 July 2002 14:09, Nils Höglund wrote: You may have to use an intermediate

RE: Remove numbers from string

2002-07-01 Thread Richard Huntley
Title: RE: Remove numbers from string select replace(translate('abcdefg123456hijkl12345abcd0','1234567890',' '),' ','') from dual / REPLACE(TRANSLAT abcdefghijklabcd First, the numbers are translated into spaces, then the spaces are cleaned up, leaving the string without

Re: Remove numbers from string

2002-07-01 Thread Jared . Still
Yes, use regular expressions via the owa_pattern package. Here's an example: declare tstr varchar2(100) := 'this c34ontains s0239everal 2340 numeric 882 dig2its'; begin dbms_output.put_line( tstr); -- remove the digits owa_pattern.change( tstr, '\d', '', 'g');

RE: Remove numbers from string

2002-07-01 Thread Grabowy, Chris
JS, Doesn't that owa_pattern package come from the PL/SQL Web Toolkit, which requires that you have the Oracle App Server licensed?? -Original Message- Sent: Monday, July 01, 2002 4:23 PM To: Multiple recipients of list ORACLE-L Yes, use regular expressions via the owa_pattern

RE: Remove numbers from string

2002-07-01 Thread Kirsch, Walter J (Northrop Grumman)
Actually, it's simpler to use the trick select translate('abc123def432', '~1234567890','~') from dual; -Original Message- Sent: Monday, July 01, 2002 4:23 PM To: Multiple recipients of list ORACLE-L Yes, use regular expressions via the owa_pattern package. Here's an example: declare

RE: Remove numbers from string

2002-07-01 Thread Khedr, Waleed
This is clever! Waleed -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Monday, July 01, 2002 5:33 PM To: Multiple recipients of list ORACLE-L Actually, it's simpler to use the trick select translate('abc123def432', '~1234567890','~') from dual; -Original Message- Sent:

RE: Remove numbers from string

2002-07-01 Thread Ferenc Mantfeld
Nils I wrote this function some time ago, when our CTI application was crashing because it found telephone numbers to dial like 1-800-Dial-a-prayer or '(800)-555 1234', so I wrote this function to replace any non-alphanumeric characters with nulls and any letters on the dial pad with the

Re: Remove numbers from string

2002-07-01 Thread Jared Still
No, I checked on my 8.1.7.3 database first, and it's there. I don't have app server installed. I have the source to the package anyway. Don't know if it's wrapped now or not, but I don't think so... Nope, not wrapped: $ORACLE_HOME/rdbms/admin/pubpat.sql

Re: Remove numbers from string

2002-07-01 Thread Jared Still
Yes, but not nearly as flexible as owa_pattern, and infinitely more difficult to read. Jared On Monday 01 July 2002 14:33, Kirsch, Walter J (Northrop Grumman) wrote: Actually, it's simpler to use the trick select translate('abc123def432', '~1234567890','~') from dual; -Original