Re: query to find string which has last 5 characters as numbers

From: Job <Job_at_pestilence.net>
Date: 2000/05/11
Message-ID: <391A727A.C455CE29@pestilence.net>#1/1

anurag wrote:

> Hi,
> How do i write a query which will search a varcharchar column
> for all the records which have their last 5 characters as
> numbers.
>
> For example
> 'abcdef 12345'
> 'abcd 12'
> 'abcdwiwiwii 1234'
>
> It should return me the 1st record only as the other 2 records
> do not have last 5 characters as numbers.
>
> Please help!

Does anyone bother to learn how to program?

create or replace function IsNumeric(iString Varchar2) return boolean is   Result boolean := FALSE;;
    slen integer;
begin

    slen := length(iString);
    if slen >= 5 then

        RESULT := TRUE;
       for i in reverse 1..slen loop
        if substr(iString,i,1) NOT IN (,'0','1','2','3','4','5','6','7','8','9')
then
            Result := FALSE;
            exit;
        end if;
       end loop;

    end if;
return(Result);
end IsNumeric;

Use a function like this in an SQL statement. Not too hard? Received on Thu May 11 2000 - 00:00:00 CDT