RE: Remove numbers from string
Really? I didn't realize you were on the advance copy list. :)
Jared
"Grabowy, Chris" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
07/02/2002 10:14 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: 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: Tuesday, July 02, 2002 12:57 AM
To: [EMAIL PROTECTED]; Grabowy, Chris
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
$ORACLE_HOME/rdbms/admin/privpat.sql
Jared
On Monday 01 July 2002 13:48, Grabowy, Chris wrote:
> 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 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');
>dbms_output.put_line( tstr);
>-- remove the extra spaces
>owa_pattern.change( tstr, '\s+', ' ', 'g');
>dbms_output.put_line( tstr);
> end;
> /
>
> Jared
>
>
>
>
>
> Nils Höglund <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 07/01/2002 04:18 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
> cc:
> Subject:Remove numbers from string
>
>
>
> 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!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Grabowy, Chris
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: 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: Tuesday, July 02, 2002 12:57 AM To: [EMAIL PROTECTED]; Grabowy, Chris 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 $ORACLE_HOME/rdbms/admin/privpat.sql Jared On Monday 01 July 2002 13:48, Grabowy, Chris wrote: > 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 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'); >dbms_output.put_line( tstr); >-- remove the extra spaces >owa_pattern.change( tstr, '\s+', ' ', 'g'); >dbms_output.put_line( tstr); > end; > / > > Jared > > > > > > Nils Höglund <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 07/01/2002 04:18 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > Subject:Remove numbers from string > > > > 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! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Remove numbers from string
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 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 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
> >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');
> >dbms_output.put_line( tstr);
> >-- remove the extra spaces
> >owa_pattern.change( tstr, '\s+', ' ', 'g');
> >dbms_output.put_line( tstr);
> > end;
> > /
> >
> > Jared
> >
> >
> >
> >
> >
> > Nils Höglund <[EMAIL PROTECTED]>
> > Sent by: [EMAIL PROTECTED]
> > 07/01/2002 04:18 AM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
> > cc:
> > Subject:Remove numbers from string
> >
> >
> >
> > 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!
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jared Still
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alexandre Gorbatchev
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Remove numbers from string
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 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
>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');
>dbms_output.put_line( tstr);
>-- remove the extra spaces
>owa_pattern.change( tstr, '\s+', ' ', 'g');
>dbms_output.put_line( tstr);
> end;
> /
>
> Jared
>
>
>
>
>
> Nils Höglund <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 07/01/2002 04:18 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> cc:
> Subject:Remove numbers from string
>
>
>
> 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!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Remove numbers from string
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 $ORACLE_HOME/rdbms/admin/privpat.sql Jared On Monday 01 July 2002 13:48, Grabowy, Chris wrote: > 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 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'); >dbms_output.put_line( tstr); >-- remove the extra spaces >owa_pattern.change( tstr, '\s+', ' ', 'g'); >dbms_output.put_line( tstr); > end; > / > > Jared > > > > > > Nils Höglund <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 07/01/2002 04:18 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > Subject:Remove numbers from string > > > > 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! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Remove numbers from string
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 corresponding numbers. You can adapt it for yourself to suit. It runs pretty fast on even millions of rows. Cheers : Regards: Ferenc Mantfeld Senior Performance Engineer Siebel Performance Engineering Melbourne, 3000, VIC, Australia Only Robinson Crusoe had all his work done by Friday -Original Message- Sent: Monday, 1 July 2002 4:18 AM To: Multiple recipients of list ORACLE-L 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:http://www.naqua.se/ Home Phone: +46 (0)18 30 09 19 Cellular Phone: +46 (0)709 91 97 65 Address:Nya Valsätrav. 26 B SE-756 46 Uppsala, Sweden -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Nils_H=F6glund?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). strip_phone.sql Description: Binary data
RE: Remove numbers from string
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: 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
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');
dbms_output.put_line( tstr);
-- remove the extra spaces
owa_pattern.change( tstr, '\s+', ' ', 'g');
dbms_output.put_line( tstr);
end;
/
Jared
Nils Höglund <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
07/01/2002 04:18 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Remove numbers from string
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:http://www.naqua.se/
Home Phone: +46 (0)18 30 09 19
Cellular Phone: +46 (0)709 91 97 65
Address:Nya Valsätrav. 26 B
SE-756 46
Uppsala, Sweden
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?Q?Nils_H=F6glund?=
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kirsch, Walter J (Northrop Grumman)
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Khedr, Waleed
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Remove numbers from string
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
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');
dbms_output.put_line( tstr);
-- remove the extra spaces
owa_pattern.change( tstr, '\s+', ' ', 'g');
dbms_output.put_line( tstr);
end;
/
Jared
Nils Höglund <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
07/01/2002 04:18 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Remove numbers from string
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:http://www.naqua.se/
Home Phone: +46 (0)18 30 09 19
Cellular Phone: +46 (0)709 91 97 65
Address:Nya Valsätrav. 26 B
SE-756 46
Uppsala, Sweden
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?Q?Nils_H=F6glund?=
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kirsch, Walter J (Northrop Grumman)
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Remove numbers from string
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 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'); dbms_output.put_line( tstr); -- remove the extra spaces owa_pattern.change( tstr, '\s+', ' ', 'g'); dbms_output.put_line( tstr); end; / Jared Nils Höglund <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 07/01/2002 04:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:Remove numbers from string 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:http://www.naqua.se/ Home Phone: +46 (0)18 30 09 19 Cellular Phone: +46 (0)709 91 97 65 Address:Nya Valsätrav. 26 B SE-756 46 Uppsala, Sweden -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Nils_H=F6glund?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Remove numbers from string
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'); dbms_output.put_line( tstr); -- remove the extra spaces owa_pattern.change( tstr, '\s+', ' ', 'g'); dbms_output.put_line( tstr); end; / Jared Nils Höglund <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 07/01/2002 04:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:Remove numbers from string 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:http://www.naqua.se/ Home Phone: +46 (0)18 30 09 19 Cellular Phone: +46 (0)709 91 97 65 Address:Nya Valsätrav. 26 B SE-756 46 Uppsala, Sweden -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Nils_H=F6glund?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Remove numbers from string
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 numbers.
-Original Message-
From: Nils Höglund [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 01, 2002 7:18 AM
To: Multiple recipients of list ORACLE-L
Subject: Remove numbers from string
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: http://www.naqua.se/
Home Phone: +46 (0)18 30 09 19
Cellular Phone: +46 (0)709 91 97 65
Address: Nya Valsätrav. 26 B
SE-756 46
Uppsala, Sweden
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?Q?Nils_H=F6glund?=
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Remove numbers from string
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 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 TRANSLATE('abc123def432'
,'A0123456789','A') "replaced" FROM DUAL;
JP
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jan Pruner
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Gorman
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Remove numbers from string
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 TRANSLATE('abc123def432'
,'A0123456789','A') "replaced" FROM DUAL;
JP
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jan Pruner
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Remove numbers from string
> 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:http://www.naqua.se/ Home Phone: +46 (0)18 30 09 19 Cellular Phone: +46 (0)709 91 97 65 Address:Nya Valsätrav. 26 B SE-756 46 Uppsala, Sweden -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Nils_H=F6glund?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Remove numbers from string
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 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!
--
Pruner Jan
[EMAIL PROTECTED]
http://jan.pruner.cz/
-
Only Robinson Crusoe had all his work done by Friday
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jan Pruner
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Remove numbers from string
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:http://www.naqua.se/ > Home Phone: +46 (0)18 30 09 19 > Cellular Phone: +46 (0)709 91 97 65 > Address:Nya Valsätrav. 26 B > SE-756 46 > Uppsala, Sweden > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com Check REPLACE and TRANSLATE in the SQL reference doc. You may have to use an intermediate stage, first replacing all numbers by say '#', then replacing '#' by ''. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
