String length mismatch not giving data in another column

Tech Learner 0
Viewed 29

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

snowflake-cloud-data-platform sql
1 Answers

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 the values in location columns are all numbers that can be treated as integers, you could also do

f.location::int = i.location::int
Phil Coulson0
Related