How to join on diff len? - SQL Server Forums

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'

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.

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]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources