Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 08 Jan 2004 @ 11:42:51 GMT


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


Subj:   Re: How to remove 'spaces' in a telephone number field using Teradata SQL
 
From:   Mirjam Berger

Hi Mike,

Interesting question. Unforutunately TD does not have a simple replace function like some other DBs. So my solution is a bit cumbersome. The first only removes blanks, the 2nd also checks that the fields are actually numbers. I think both will work, but I'm interested if somebody else has an easier solution...

select
trim(substring(tel_numb from 1 for 1)) ||
trim(substring(tel_numb from 2 for 1)) ||
trim(substring(tel_numb from 3 for 1)) ||
trim(substring(tel_numb from 4 for 1)) ||
trim(substring(tel_numb from 5 for 1)) ||
trim(substring(tel_numb from 6 for 1)) ||
trim(substring(tel_numb from 7 for 1)) ||
trim(substring(tel_numb from 8 for 1)) ||
trim(substring(tel_numb from 9 for 1)) ||
trim(substring(tel_numb from 10 for 1)) ||
trim(substring(tel_numb from 11 for 1))
tel_numb_no_blanks, tel_numb
from temp_tables.tel

select
(case when substring(tel_numb from 1 for 1) in ('1',
'2', '3', '4', '5', '6', '7','8', '9', '0') then
substring(tel_numb from 1 for 1) else '' end) ||

(case when substring(tel_numb from 2 for 1) in ('1',
'2', '3', '4', '5', '6', '7','8', '9', '0') then
substring(tel_numb from 2 for 1) else '' end) ||

(case when substring(tel_numb from 3 for 1) in ('1',
'2', '3', '4', '5', '6', '7','8', '9', '0') then
substring(tel_numb from 3 for 1) else '' end) ||

(case when substring(tel_numb from 4 for 1) in ('1',
'2', '3', '4', '5', '6', '7','8', '9', '0') then
substring(tel_numb from 4 for 1) else '' end) ||

(case when substring(tel_numb from 5 for 1) in ('1',
'2', '3', '4', '5', '6', '7','8', '9', '0') then
substring(tel_numb from 5 for 1) else '' end) ||

(case when substring(tel_numb from 6 for 1) in ('1',
'2', '3', '4', '5', '6', '7','8', '9', '0') then
substring(tel_numb from 6 for 1) else '' end) ||

(case when substring(tel_numb from 7 for 1) in ('1',
'2', '3', '4', '5', '6', '7','8', '9', '0') then
substring(tel_numb from 7 for 1) else '' end) ||

(case when substring(tel_numb from 8 for 1) in ('1',
'2', '3', '4', '5', '6', '7','8', '9', '0') then
substring(tel_numb from 8 for 1) else '' end) ||

(case when substring(tel_numb from 9 for 1) in ('1',
'2', '3', '4', '5', '6', '7','8', '9', '0') then
substring(tel_numb from 9 for 1) else '' end) ||

(case when substring(tel_numb from 10 for 1) in ('1',
'2', '3', '4', '5', '6', '7','8', '9', '0') then
substring(tel_numb from 10 for 1) else '' end) ||

(case when substring(tel_numb from 11 for 1) in ('1',
'2', '3', '4', '5', '6', '7','8', '9', '0') then
substring(tel_numb from 11 for 1) else '' end)

 tel_numb_no_blanks, tel_numb
from temp_tables.tel

Thanks,

Mirjam Berger



     
  <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: 27 Dec 2016