I need to join data from 2 tables where the common field is in varying formats/lengths. In Table A, Acct_Nb is Char 20, though many of the accounts are only about 10 characters. In Table B, Acct_Num is Char 50, though the accounts are zero filled out to 20 chars each (not sure why, since the field is char 50). So I need to join on the account columns that are different lengths (50 and 20) and in which one is zero filled and the other is not. I've tried a variety of versions of CAST, but have not had any luck with it. An example would be:
INNER JOIN TABLE B ON CAST(A.ACCT_NB AS CHAR(50))=B.ACCT_NUM
Help on how to get rid of the leading zeros in the B.ACCT_NUM and then setting up the CAST would be appreciated.
You can use a combination of TRIM and cast: Try this:
TRIM (BOTH '0' FROM CAST(A.ACCT_NB AS CHAR(50))) or varchar.
You don't have to care about CHARs or VARCHARs, because trailing spaces simply don't matter, e.g. '0123' and '0123 ' compare equal.
But the leading zeroes (or leading blanks) cause trouble and you need to get rid of them. You should use Raja's approach, just change BOTH to LEADING.
Thank you gentlemen. I was able to get the following to work:
INNER JOIN TABLE B ON CAST((SUBSTR('00000000000000000000',1,20-CHARS(TRIM(A.ACCT_NB)))||A.ACCT_NB) AS CHAR(50)) = B.ACCT_NUM
Perhaps the Trim wasn't necessary and maybe I didn't need to Cast to Char(50) for this Join? Not sure, but using the Substring to get the leading zeros solved the problem.
Seems like the example presented above of Trim(Leading '0' from Cast(... would have been a bit easier though. Wish I had seen this prior to coming up with the Substr method. Will have to remember that in the future. Thanks again!
I would strongly recommend to change the join-condition to a less resource-consuming and easier-to-understand
INNER JOIN TABLE B ON A.ACCT_NB = TRIM(LEADIMNG '0' FROM B.ACCT_NUM)
In fact, both are bad and in best case you should try to clean up both your data model and data.
Yeah, I agree that the the method you just posted is the best/most efficient approach and I changed my code to that. I am an end user, so unfortunately I have no control over the data or the tables. It would be nice if our IT dept would standardize these fields as they load them into the system so that I don't have to create inefficient work-arounds in SAS when I'm querying TD.
I was about to commend on the modeling side. At least , this should have been caught in pdm or even in design phase.