declare @address_type table
(
abr varchar(10),
description varchar(50)
)

declare @address table
(
street_name varchar(50)
)

insert into @address_type
select 'ln', 'lane' union all
select 'rd', 'road' union all
select 'ave', 'avenue'

insert into @address
select 'apple road' union all
select 'orage lane' union all
select 'lane orage lane' union all
select 'apple road road'



select * from @address
select 
	len(a.street_name)+1-charindex(' ', reverse(a.street_name))  as LeftGrabAmount,
	charindex(' ', reverse(a.street_name)) ,
	ltrim(reverse(substring(reverse(a.street_name), 1, charindex(' ', reverse(a.street_name))))) ,
	substring(a.street_name ,0,len(a.street_name)+1-charindex(' ', reverse(a.street_name))),
--	replace(substring(a.street_name,len(a.street_name)+1-charindex(' ', reverse(a.street_name)),charindex(' ', reverse(a.street_name))), adt.description, adt.abr),
	substring(a.street_name ,0,len(a.street_name)+1-charindex(' ', reverse(a.street_name))) + replace(substring(a.street_name,len(a.street_name)+1-charindex(' ', reverse(a.street_name)),charindex(' ', reverse(a.street_name))), adt.description, adt.abr)
from @address a
	inner join
	@address_type adt
		on ltrim(reverse(substring(reverse(a.street_name), 1, charindex(' ', reverse(a.street_name))))) = adt.description

update @address
set street_name = substring(a.street_name ,0,len(a.street_name)+1-charindex(' ', reverse(a.street_name))) + replace(substring(a.street_name,len(a.street_name)+1-charindex(' ', reverse(a.street_name)),charindex(' ', reverse(a.street_name))), adt.description, adt.abr)
from @address a , @address_type adt
where ltrim(reverse(substring(reverse(a.street_name), 1, charindex(' ', reverse(a.street_name))))) = adt.description
select *from @address

street_name
--------------------------------------------------
apple road
orage lane
lane orage lane
apple road road

LeftGrabAmount                                                                                                                   
-------------- ----------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6              5           road                                               apple                                              apple rd
6              5           lane                                               orage                                              orage ln
11             5           lane                                               lane orage                                         lane orage ln
11             5           road                                               apple road                                         apple road rd

street_name
--------------------------------------------------
apple rd
orage ln
lane orage ln
apple road rd