Using substr in a join with varying lengths

Database
Enthusiast

Using substr in a join with varying lengths

Hello friends,

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.

 

SELECT DISTINCT

   T1.ACCT_NUM

  ,T2.VAR2

  ,T2.VAR2

 

       FROM table1 AS T1

LEFT JOIN table 2 AS T2

ON T1.ACCT_NUM = T2.ACCT_NUM

 

Ex. 11111111111111

      2222222222222

      3333333333333

      4444444444444

      5555555555555

--ON       CAST(SUBSTR('00000000000000000000000',1,23-CHARS(t1.acct_num))||TRIM(t1.acct_num) AS VARCHAR(23))   = t2.acct_num; 

 

 

3 REPLIES
Junior Contributor

Re: Using substr in a join with varying lengths

What are the datatypes of those columns?

 

Enthusiast

Re: Using substr in a join with varying lengths

The T1.ACCT_NUM is varchar(23) and the T2.ACCT_NUM is char(23)

 

 

Highlighted
Junior Contributor

Re: Using substr in a join with varying lengths

Unless you got leading spaces (which should never happen)  you can join both columns directly, any trailing spaces will e ignored during comparison.