Skip to content
Advertisement

SQL Remove all leading zeros

My join returns null because I am not able to remove the leading zeros. Basically, I am trying to replace the pol.Policy_Number with src.Policy_Number for a large number of policies. Is there a way I can remove the leading 0s in the ON statement for src.Policy_Number? I think Padding is needed, but I am new to SQL so any help would be much appreciated. That pol policy numbers always equal src policy numbers if it was not for the leading 0s in pol This is not a duplicate. I am not looking for a select. I need to put padding next to the ON statement so any leading 0s from src.Policy_Number will be removed so the JOIN can return rows. Please don’t close the question a duplicate

 UPDATE pol
SET pol.Policy_Number = src.Policy_Number
FROM Policy_Table pol
INNER JOIN SourceTable src
 on  pol.Policy_Number =src.Policy_Number--00000000000000ABCD200,0000000000000EFT200
 Where src.Policy_Number IN('ABCD200','EFT200')

Advertisement

Answer

 UPDATE pol
SET pol.Policy_Number = src.Policy_Number
FROM Policy_Table pol
INNER JOIN SourceTable src
on  pol.Policy_Number =replace(ltrim(replace(src.Policy_Number,'0',' ')),' ','0')
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement