Re: to_Char or not to_Char

From: DL <donlcs_at_yahoo.com>
Date: 16 Jun 2002 20:57:57 -0700
Message-ID: <604bb90c.0206161957.7c611dc0_at_posting.google.com>


Ken,

Your code is interesting. I'm clear about the issue I raised. Thanks.

Don

Ken Denny <ken_at_kendenny.com> wrote in message news:<Xns922D9F4ECD899kendenny_at_65.82.44.7>...
> donlcs_at_yahoo.com (DL) wrote in
> news:604bb90c.0206140852.248232ca_at_posting.google.com:
>
> > Will,
> >
> > Ha, now I see why Sybrand Bakker said "this is a lousy datamodel". My
> > question may not have been clear. What I wanted to do is, for
> > "string" type of data type (char,varchar2,nchar,nvarchar2), use
> > To_char function, so,
> > for n* string data type, the SQL statement does not have to use N'
> > prefix.
> > This comes handy, when the program does not have knoledge beforehand
> > of the database schema (structure). Of course, the overhead is to
> > determine data type first.
> >
> > And certainly, I agree with you on
> > "you should *only* have numbers in
> > number columns or strings in string columns or dates in date columns,
> > etc.".
> >
> > Thanks.
> >
> > Don
>
> Don, Here's something you might find useful. I did it some years ago for a
> project I was working on. It's a function that will return a comparison of
> two strings. If both strings contain numeric data it do a numeric
> comparison, otherwise it does a string comparison. It also requires
> another function, is_number to test whether the strings contain numerics.
> Here are both functions:
>
> FUNCTION is_number (numstring VARCHAR2) RETURN BOOLEAN
> IS
> /*
> || Returns true if the string contains a valid number
> */
> w_num NUMBER;
> BEGIN
> w_num := TO_NUMBER(numstring);
> RETURN TRUE;
> EXCEPTION
> WHEN INVALID_NUMBER OR VALUE_ERROR THEN
> RETURN FALSE;
> END is_number;
>
> FUNCTION cmpar(string1 VARCHAR2, string2 VARCHAR2)
> RETURN NUMBER
> IS
> /*
> || This function is useful when comparing character strings which may
> || contain numeric data. It compares, two character strings. If both
> || contain numeric data then it converts both to numerics and compares
> || them, otherwise it does a string comparison. It returns 0 if the
> || strings are equal, a negative if string2 is greater than string 1 and a
> || positive if string1 is greater than string2.
> */
> BEGIN
> IF is_number(string1) AND is_number(string2)
> THEN
> RETURN TO_NUMBER(string1) - TO_NUMBER(string2);
> END IF;
> IF string1 > string2
> THEN
> RETURN 1;
> ELSIF string1 < string2
> THEN
> RETURN -1;
> ELSE
> RETURN 0;
> END IF;
> END cmpar;
Received on Mon Jun 17 2002 - 05:57:57 CEST

Original text of this message