Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 24 Apr 2007 @ 21:42:34 GMT

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

Subj:   Re: Phone number character data cleaning?
From:   Curley, David

I don't about clever, but this might work: for each position in the field, turn it into 1-9 or an empty string if not a number, concatenate the results, and cast as integer.

Something like

     Select cast(
     case when substr(phone_number,1,1)  between '1' and '9' then
     substr(phone_number,1,1)  else '' end
     case when substr(phone_number,2,1)  between '1' and '9' then
     substr(phone_number,2,1)  else '' end
     case when substr(phone_number,3,1)  between '1' and '9' then
     substr(phone_number,3,1)  else '' end
     case when substr(phone_number,4,1)  between '1' and '9' then
     substr(phone_number,4,1)  else '' end
     ... Etc....
     case when substr(phone_number,,1)  between '1' and '9'
     then substr(phone_number,,1)  else '' end
     As integer)


     select cast(
     case when substr('1/2',1,1) between '1' and '9' then substr('1/2',1,1)
     else '' end
     case when substr('1/2',2,1) between '1' and '9' then substr('1/2',2,1)
     else '' end
     case when substr('1/2',3,1) between '1' and '9' then substr('1/2',3,1)
     else '' end
     as integer) as now_a_number;



  <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: 15 Jun 2023