sql - Join on project number -

i try join 2 tables based on project number. in table 'dbo.transactions' projectnr has prefix (e.g. 1110001'), not in 'dbo.projects' table (e.g. 0001).

select a.projectnr, b.description dbo.transactions  right join dbo.projects b on a.projectnr = b.projectnr 

the a.projectnr (1110001) should join on b.projectnr. tried use stuff strip first 3 numbers, don't know how mix in inner join:

stuff(a.projectnr, 1, 3, '') 

select a.projectnr, b.description  dbo.transactions    right join dbo.projects b on stuff(a.projectnr, 1, 3, '') = b.projectnr 

keep in mind absolute worst thing can do, won't use indices @ all. once either table reaches large size, you'll bog down system waiting on query.

you might have better luck following:

select a.projectnr, b.description  (select *, stuff(a.projectnr, 1, 3, '') normalprojectnr dbo.transactions)    right join dbo.projects b on normalprojectnr = b.projectnr 

but still won't use indices. if grows large enough, first select transactions temporary table column, apply index on manually, , join.