Changing Phone Number Format

Database
Enthusiast

Changing Phone Number Format

Hello,

I have some code that I wrote that joins 2 separate tables:

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

The only problem is, MTN_1 is in '999-999-9999' (VARCHAR) format.  MTN is in '9999999999' (Character Fixed) format.  How can I change the Character Fixed format to match the 999-999-9999 format so I can use the columns in my join conditions?

Tags (3)
2 REPLIES
Enthusiast

Re: Changing Phone Number Format

Try with the below,

sel * from mkformat_1 A
join
mkformat_2 B
on (cast(A.mtn as bigint) (format '999-999-9999')) (varchar(20)) = B.mtn

Regards,

Mohan

Enthusiast

Re: Changing Phone Number Format

You can also try something like as it the phone number and lenght is going to be the same all the time -

SELECT CAST((SUBSTR('999-999-9999',1,3) || SUBSTR('999-999-9999',5,3) || SUBSTR('999-999-9999',9,4)) AS CHAR(10))