Archives of the TeradataForum

Message Posted: Wed, 10 Sep 2003 @ 18:14:14 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: IS_NUMBER

From:		Dieter Noeth

Anomy Anom wrote:

I'm working in converting some SQL scripts from Oracle to Teradata. Oracle SQL is using an user defined function named 'is_number' witch returns true if a string argument can be converted to number;

Ex.

is_number('ABC') -> returns FALSE
is_number('123') -> returns TRUE

Any idea about how to implement something similar with Teradata?

For a complex scenario Geoffrey is right, it's gets really ugly ;-)

But if there are only characters 'a' to 'z' then there's a simple algorithm:

When UPPER(col) = LOWER(col), then it's numeric:

SELECT CAST(col AS INT)
FROM
  (
   SELECT
     SUBSTRING('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' FROM RANDOM(1,100) FOR 1) ||
     SUBSTRING('0123456789' FROM RANDOM(1,20) FOR 1) ||
     SUBSTRING('0123456789' FROM RANDOM(1,10) FOR 1) ||
     SUBSTRING('0123456789' FROM RANDOM(1,20) FOR 1) ||
     SUBSTRING('0123456789' FROM RANDOM(1,10) FOR 1) ||
     SUBSTRING('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZäöü' FROM RANDOM(1,100) FOR 1) ||
     SUBSTRING('0123456789' FROM RANDOM(1,20) FOR 1) ||
     SUBSTRING('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' FROM RANDOM(1,100) FOR 1) ||
     SUBSTRING('0123456789' FROM RANDOM(1,10) FOR 1) AS col
   FROM sys_calendar.calendar
  ) dt
WHERE UPPER(col) = LOWER(col) (cs)
;

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>