All Forums Database
ciw916 8 posts Joined 11/12
28 Mar 2013
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?

mohan.mscss 31 posts Joined 04/11
29 Mar 2013

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

KS42982 137 posts Joined 12/12
29 Mar 2013

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

You must sign in to leave a comment.