将电话号码格式转换为'XXX-XXX-XXXX'

Question

我用Teradata写了一些代码来连接两个独立的表:

SELECT
A.*,
B.MTN_1
FROM
TABLE_1 AS A
JOIN
TABLE_2 AS B
ON A.ACCT_NUM=B.ACCT_NUM
WHERE A.MTN=B.MTN_1

唯一的问题是，MTN_1是999-999-9999 (VARCHAR)格式。MTN为9999999999 (Character Fixed)格式。如何更改Character Fixed格式以匹配999-999-9999格式，以便在连接条件中使用这些列?

Answer 1

带有替换功能:

SELECT A.*, B.MTN_1
FROM TABLE_1 AS A
JOIN TABLE_2 AS B ON A.ACCT_NUM=B.ACCT_NUM
WHERE A.MTN=REPLACE(B.MTN_1,"-","")

当使用没有替换函数的Teradata时，您可以使用substr和连接操作符:

SELECT A.*, B.MTN_1
FROM TABLE_1 AS A
JOIN TABLE_2 AS B ON A.ACCT_NUM=B.ACCT_NUM
WHERE A.MTN=SUBSTR(B.MTN_1,1,3) || SUBSTR(B.MTN_1,5,3) || SUBSTR(B.MTN_1,9,4)