Re: number only

From: Mladen Gogala <mladen_at_wangtrading.com>
Date: Wed, 30 Jun 2004 10:09:12 -0400
Message-ID: <20040630140912.GA2382@mladen.wangtrading.com>

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_at_hotmail.com> 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_at_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
-----------------------------------------------------------------

Received on Wed Jun 30 2004 - 09:07:50 CDT