 |
 |
Archives of the TeradataForum
Message Posted: Mon, 15 Aug 2005 @ 13:57:58 GMT
| Subj: | | Re: How to Substring data |
| |
| From: | | Diehl, Robert |
It looks like you are trying to remove non numerics Try this (you will have to change phone_num to your column name. You will also hve to
add or subtract characters based on the length
nullif(( trim(both from (case when substring(phone_num from 1 for 1)
between '0' and '9' then substring(phone_num from 1 for 1) else ' ' end
))
|| trim(both from (case when substring(phone_num from 2 for 1) between
'0' and '9' then substring(phone_num from 2 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 3 for 1) between
'0' and '9' then substring(phone_num from 3 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 4 for 1) between
'0' and '9' then substring(phone_num from 4 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 5 for 1) between
'0' and '9' then substring(phone_num from 5 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 6 for 1) between
'0' and '9' then substring(phone_num from 6 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 7 for 1) between
'0' and '9' then substring(phone_num from 7 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 8 for 1) between
'0' and '9' then substring(phone_num from 8 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 9 for 1) between
'0' and '9' then substring(phone_num from 9 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 10 for 1) between
'0' and '9' then substring(phone_num from 10 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 11 for 1) between
'0' and '9' then substring(phone_num from 11 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 12 for 1) between
'0' and '9' then substring(phone_num from 12 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 13 for 1) between
'0' and '9' then substring(phone_num from 13 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 14 for 1) between
'0' and '9' then substring(phone_num from 14 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 15 for 1) between
'0' and '9' then substring(phone_num from 15 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 16 for 1) between
'0' and '9' then substring(phone_num from 16 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 17 for 1) between
'0' and '9' then substring(phone_num from 17 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 18 for 1) between
'0' and '9' then substring(phone_num from 18 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 19 for 1) between
'0' and '9' then substring(phone_num from 19 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 20 for 1) between
'0' and '9' then substring(phone_num from 20 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 21 for 1) between
'0' and '9' then substring(phone_num from 21 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 22 for 1) between
'0' and '9' then substring(phone_num from 22 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 23 for 1) between
'0' and '9' then substring(phone_num from 23 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 24 for 1) between
'0' and '9' then substring(phone_num from 24 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 25 for 1) between
'0' and '9' then substring(phone_num from 25 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 26 for 1) between
'0' and '9' then substring(phone_num from 26 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 27 for 1) between
'0' and '9' then substring(phone_num from 27 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 28 for 1) between
'0' and '9' then substring(phone_num from 28 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 29 for 1) between
'0' and '9' then substring(phone_num from 29 for 1) else ' ' end ))
|| trim(both from (case when substring(phone_num from 30 for 1) between
'0' and '9' then substring(phone_num from 30 for 1) else ' ' end ))), '
')
Thanks,
Bob Diehl
| |
