Re: number only

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' <oracle_list@xxxxxxxxxxx> 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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: