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')