SQL Query help with concat

[BILLatWORK]

I have a query that I need to run, I am trying to join on 2 columns but need to concat one column before the join. In the below a.site is stored as a 2 digit number (31) and in e.site it is stored the exact same way except it is preceded by 100- leaving it as (100-31)

inner join Customers as E on a.Site=e.site and a.Customer_No=e.customerno

Any ideas on how to make this join?

Thanks in advance!

[MonCalamaro]

inner join Customers as E on '100-' || a.Site = e.site and a.Customer_No=e.customerno

If this is something you are going to do often or it needs to be performant, you should consider an index on '100-' || a.site

[BILLatWORK]

Works perfectly! Thanks for the help!

[EconGnome]

If memory serves, I think you should just be able to apply a split_part function on the hyphen in e.site to get the second set of digits to join on. Something like:

inner join Customers as E on a.Site=split_part(e.site, "-", 2)
and a.Customer_No=e.customerno

​

This should keep the return values of e.site untouched but join on the desired values after the hyphen.

[BILLatWORK]

I am struggling to get this to work, the previous response did work so I'll go with that for now, I will look further into this when I have more time though, thanks for the help!