Archives of the TeradataForum

Message Posted: Mon, 23 Jul 2007 @ 12:41:55 GMT

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

          > UNION
          > SELECT '7A' COL
          > FROM sys_calendar.calendar
          > WHERE calendar_date = DATE
          >
          > :-)

:-(

You know I don't like the UPPER=LOWER comparison as it does not filter out not only special characters but also some letters.

The idea I posted earlier handles only the first position. Here is the variation for the cases when you expect strange characters in both the first and the second positions:

     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
     union
     select 'A7' COL
     from sys_calendar.calendar
     union
     select '7#' COL
     from sys_calendar.calendar
     ) DT
     where substring(DT.COL from 1 for 1) >= '0'
     and substring(DT.COL from 1 for 1) <= '9'
     and substring(DT.COL from 2 for 1) >= '0'
     and substring(DT.COL from 2 for 1) <= '9' ;

The query above is only an illustration of using string comparison before the CAST operation. There are quite a few aspects in it which may differ from the OP's case: all the literals are by default UNICODE VARCHAR(2), CASE sensitivity depends on the session mode, collation can be session specific, etc.

Regards,

Victor

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