Archives of the TeradataForum

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

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

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