Re: how to sort numbers in varchar2

I counted on such a comment....now, if the column contains text, then a whole new problem is raised : How should letters be sorted/compared to decimals ? The OP didn't elaborate that, so every suggested solution would be a shot in the haze, unless we assumed that only numbers were stored.

Anyway, there is an easy way to filter everything but numbers out :

create or replace function is_number(pchar in varchar2) return number as
  i number;
begin
  i := to_number(pchar);
 return 1;
 exception
when others then
 return 0;
end is_number;

and then :

select *
from mytab
where is_number(mycol) = 1
order by 1

On 8 Jul 2003 23:33:03 -0700, janik_at_pobox.sk (Jan) wrote:

>Yours has also one big drawback,
>
>Original problem was:
>
>"I have a varchar2 column which stores characters as well as numbers in
>string such as '12345'. "
>
>so if you do TO_NUMBER(col1) and there is a text it will raise an error.
>
>
>Kenneth Koenraadt wrote in message news:<3f0ad068.448094_at_news.inet.tele.dk>...
Received on Sat Jul 12 2003 - 03:16:08 CDT