Archives of the TeradataForum
Message Posted: Fri, 20 Jul 2007 @ 15:06:38 GMT
Subj: | | Re: Eternally Foolproof Casting from text to number |
|
From: | | Dieter Noeth |
Victor Sokovin wrote:
| If you don't mind assuming a certain character ordering (collation) then you might try the following idea: | |
> select *
>>from (
> select 'A7' COL
>>from sys_calendar.calendar
> union
> select '77' COL
>>from sys_calendar.calendar
> union
> select '#7' COL
>>from sys_calendar.calendar
> union
> select '.7' COL
>>from sys_calendar.calendar
> union
> select '%?' COL
>>from sys_calendar.calendar
> ) DT
> where DT.COL >= '00'
> and DT.COL <= '99';
UNION
SELECT '7A' COL
FROM sys_calendar.calendar
WHERE calendar_date = DATE
:-)
If you add "AND UPPER(COL) = LOWER(COL)" it will still fail for '7#' :-(
Would be nice to have a GREP-function (or better SQL:2003 SIMILAR)...
Dieter
|