I’ve a column location in my table. I’ve used it as a common column to join two tables
1.Factory
2.Inventory
Like
Factory.location = inventory.location
But I’m having a problem..
For example Location in factory table just has 09 where inventory table has 009 doesn’t match but for three digit numbers it’s matching eg: 115 = 115, 999=999. But in the output, I still get 009 but I’m not getting data in another column for the ones which doesn’t have three digits.
Please tell me how to make it 3 digit and join.
I tried putting it as
(Case when
length(factory.location)<3
Then concat(‘0’,factory.location)
else inventory.location
End) as location
in select statement.
Please help
>Solution :
Assuming you’re storing them as varchar for a reason, you could pad them to equalize length before matching. I chose 3 because it sounded like all the locations are 3 characters long. Change as required
lpad(f.location, 3, '0') = lpad(i.location, 3, '0')
If you the values in location columns are all numbers that can be treated as integers, you could also do
f.location::int = i.location::int