Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

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


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


Subj:   Re: Eternally Foolproof Casting from text to number
 
From:   Victor Sokovin

          > 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#'  



:-(

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020