Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to join on diff len?

Author  Topic 

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2014-10-28 : 18:16:07
I have File A. it has an order number that is len of 8.

I want to join on File B. the order number there is len of 10 left justified.

Something like

Select * from File A
Inner Join on File B where AOrder = BOrder (Substr 1,8)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-28 : 18:40:12
quote:
Originally posted by AdamWest

I have File A. it has an order number that is len of 8.

I want to join on File B. the order number there is len of 10 left justified.

Something like

Select * from File A
Inner Join on File B where AOrder = BOrder (Substr 1,8)



assuming the longer field is padded with spaces, you don't need the substring function. In SQL,

'A ' = 'A'
Go to Top of Page

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2014-10-28 : 18:57:03
oh sorry my bad.

they add usually a '01' or some number at the end so that we do need
to substring unfortunately.
Go to Top of Page

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-10-28 : 20:08:05
[code]
SELECT *
FROM [File A]
INNER JOIN [File B]
ON AOrder = LEFT(BOrder, 8)
[/code]
Go to Top of Page
   

- Advertisement -