query to find string which has last 5 characters as numbers

[anurag]

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!

Thanks
Anurag

also reply at
amin...@UNIBIZ.COM

* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!

[Michel Cadot]

You can use something like that:
select * from tab
where translate(col,'0123456789','0000000000') like '%00000';

--
Have a nice day
Michel

anurag <aminocha...@unibiz.com.invalid> a écrit dans le message :
02a788f0...@usw-ex0104-033.remarq.com...

[Job]

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?