MySQL - JOIN based on CONVERT column for NUMERIC

Question

Basically, I am trying to convert a table field while doing a join. The numbers in the field begin with 0009897896, 000239472938, 00032423, etc.

I want to do a connection-based conversion, so when I request a column, it displays numbers without leading 0, so they will display as 9897896, 239472938, 32423, etc.

Can someone help me do this? I got stuck in this problem for a while :(

Here is what I have so far ... joined another database:

SELECT l.loannumber AS '1 Loan number',
       fl.loan_num AS '2 Loan number',
       CASE
           WHEN loannumber<>fl.loan_num THEN "YES"
           ELSE "NO"
       END AS "issue?"
FROM loan l
LEFT JOIN cware_cms.file_lst fl ON (CONVERT(SUBSTRING_INDEX(l.LoanNumber, '-', -1), UNSIGNED INTEGER)) = (CONVERT(SUBSTRING_INDEX(fl.loan_num, '-', -1),UNSIGNED INTEGER))
LEFT JOIN cware_cms.case_lst cl ON cl.case_id =
  (SELECT MAX(cware_cms.file_case.case_id))
FROM cware_cms.file_case
INNER JOIN cware_cms.case_lst ON cware_cms.file_case.case_id = cware_cms.case_lst.case_id

Answer 1

I think you want to get TRIM LEADING zeros. This will do the trick:

LEFT JOIN cware_cms.file_lst fl ON TRIM(LEADING '0' FROM l.loannumber) = TRIM(LEADING '0' FROM fl.loan_num)