Archives of the TeradataForum
Message Posted: Sun, 22 Sep 2002 @ 08:01:28 GMT
| Subj: | | Re: Phone number matching from dec to char |
| |
| From: | | Dieter N�th |
Burton, Bruce wrote:
| | I have two tables to match by phone number. Phone number in either case is in a standard 10 byte format with npa, nxx, line. | |
US standard? Always 10 chars? Any leading zeros/trailing blanks possible?
| | table 1 phone# decimal(10,0)
table 2 phone# char(10) | |
Looks like a bug in your data model ;-)
| | I've tried casting phone1 as char(10) and phone2 as dec(10,0) but no luck matching...comes up with a truncation error when
casting phone1 as char(10) | |
you probably have a standard format '----------9.':
1234567890 will be ' 1234567890.'
a Teradata cast will work, but cut off the last digit
select 1234567890 (dec(10,0)) as d, format (d), d (char(10));
*** Query completed. One row found. 3 columns returned.
*** Total elapsed time was 1 second.
d Format(d) d
------------ ------------------------------ ----------
1234567890. ----------9. 123456789
an ANSI cast will return an error instead of a wrong result
select 1234567890 (dec(10,0)) as d, format (d), cast(d as char(10));
*** Failure 3996 Right truncation of string data.
| | and comes up with 'bad data' when casting phone 2 as dec(10,0). | |
There must be a non-digit character in phone 2:
select '1234567890' (dec(10,0));
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
'1234567890'
------------
1234567890.
| | I finally got the match to work when I did a substring: | |
| | where substr(phone1,2,10))=phone2 | |
| | Not sure why this worked. | |
the substring strips of the sign
| | Any suggestions on the best way to cast either one of the variables? | |
leading zeros:
cast((table1.phone#(format '9999999999')) as char(10)) = table2.phone#
trailing blanks:
cast((table1.phone#(format 'zzzzzzzzzz')) as char(10)) = table2.phone#
leading blanks:
table1.phone#(format 'zzzzzzzzzz')(char(10)) = table2.phone#
Dieter
| 
