Re: to_Char or not to_Char

From: Ken Denny <ken_at_kendenny.com>
Date: Fri, 14 Jun 2002 19:26:13 GMT
Message-ID: <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;

-- 
Ken Denny
http://www.kendenny.com/

A clear conscience is usually the sign of a bad memory.

Received on Fri Jun 14 2002 - 21:26:13 CEST