I'm struggling with finding the right data manipulation to allow account numbers of varying lengths to join correctly. Most of my account numbers are 13 digits long but one of them is 14 digits. I have tried various string functions but I have not found a combination that allows me to return a match on account numbers that are BOTH 13 and 14 digits long. Can someone please help me? Many thanks in advance for your help - it is greatly appreciated.
FROM table1 AS T1
LEFT JOIN table 2 AS T2
ON T1.ACCT_NUM = T2.ACCT_NUM
--ON CAST(SUBSTR('00000000000000000000000',1,23-CHARS(t1.acct_num))||TRIM(t1.acct_num) AS VARCHAR(23)) = t2.acct_num;
Unless you got leading spaces (which should never happen) you can join both columns directly, any trailing spaces will e ignored during comparison.