From oracle-l-bounce@freelists.org Wed Jun 30 09:07:50 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5UE7PX20720 for ; Wed, 30 Jun 2004 09:07:35 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i5UE7E620669 for ; Wed, 30 Jun 2004 09:07:24 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 60BA872C0FD; Wed, 30 Jun 2004 08:48:47 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16160-33; Wed, 30 Jun 2004 08:48:47 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B4D4C72CAE3; Wed, 30 Jun 2004 08:48:33 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 30 Jun 2004 08:46:35 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6482972C7EA for ; Wed, 30 Jun 2004 08:46:34 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16018-07 for ; Wed, 30 Jun 2004 08:46:34 -0500 (EST) Received: from smtp.wangtrading.com (smtp.wangtrading.com [167.206.68.5]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C290B72C75B for ; Wed, 30 Jun 2004 08:46:33 -0500 (EST) Received: from imap (Not Verified[192.168.9.50]) by smtp.wangtrading.com with NetIQ MailMarshal (v5.5.5.8) id ; Wed, 30 Jun 2004 10:07:41 -0400 Received: from mladen.wangtrading.com (mladen.wangtrading.com [::ffff:192.168.3.47]) (TLS: TLSv1/SSLv3,256bits,AES256-SHA) by imap with esmtp; Wed, 30 Jun 2004 10:09:12 -0400 Received: from mladen (localhost.localdomain [127.0.0.1]) by mladen.wangtrading.com (8.12.8/8.12.8) with ESMTP id i5UE9C7V002411 for ; Wed, 30 Jun 2004 10:09:12 -0400 Date: Wed, 30 Jun 2004 10:09:12 -0400 From: Mladen Gogala To: oracle-l@freelists.org Subject: Re: number only Message-ID: <20040630140912.GA2382@mladen.wangtrading.com> References: <200406301338.i5UDcsW22651@noc.nexlink.net> Mime-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Content-Disposition: inline In-Reply-To: <200406301338.i5UDcsW22651@noc.nexlink.net> (from sfaroult@roughsea.com on Wed, Jun 30, 2004 at 09:38:54 -0400) X-Mailer: Balsa 2.0.17 Lines: 114 X-Mime-Autoconverted: from 8bit to 7bit by courier 0.42 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 4074 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mladen@wangtrading.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Here is the complete anser, with syntax: Test case setup (scott/tiger) SQL> create table testtab (charcol varchar2(10)); Table created. SQL> insert into testtab values ('&value'); Enter value for value: abc123 old 1: insert into testtab values ('&value') new 1: insert into testtab values ('abc123') 1 row created. SQL> / Enter value for value: 123 old 1: insert into testtab values ('&value') new 1: insert into testtab values ('123') 1 row created. SQL> / Enter value for value: abcde old 1: insert into testtab values ('&value') new 1: insert into testtab values ('abcde') 1 row created. SQL> commit; Commit complete. Program unit: #!/usr/bin/perl -w use strict; use DBI; my $dbh = db_connect( "scott", "tiger" ); my $NUMCOL = "select charcol from testtab"; my $sth = $dbh->prepare($NUMCOL); my $charcol; $sth->execute(); $sth->bind_col( 1, \$charcol ); while ( $sth->fetch() ) { print get_numbers($charcol), "\n"; } END { if ( defined $dbh ) { $dbh->disconnect(); } } sub db_connect { my ( $username, $passwd, $db ) = ( @_, $ENV{"TWO_TASK"} ); die "Undefined connection string.\n" unless defined $db; my $dbh = DBI->connect( "dbi:Oracle:$db", $username, $passwd ); $dbh->{AutoCommit} = 0; $dbh->{RaiseError} = 1; $dbh->{ora_check_sql} = 0; $dbh->{RowCacheSize} = 16; return ($dbh); } sub get_numbers { my $str = shift; my $num=999; if ( $str =~ /([0-9]+)/ ) { $num = $1; } return($num); } Execution: Yogi> ./numeric 123 123 999 Feel free to modify it as you find fit. On 06/30/2004 09:38:54 AM, Stephane Faroult wrote: > RTF SQL Reference, functions, TRANSLATE(). With a pinch of DECODE(LENGTH(), > 0, 999, ...) it should do. > Regards, > > Stephane Faroult > > On Wed, 30 Jun 2004 14:34 , 'Oracle' sent: > > I have a column with data such as : > > abc123 > 123 > abcde > > I want to write a query which returns only numbers from the column , if > there are none, then default to 999 > So i would have: > > 123 > 123 > 999 > > Any ideas on the syntax? > > Thanks > ---------------------------------------------------------------- -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------