SET BASED Solution To This Problem?

Greetings all,

Looking for a set based solution to the following SQL problem. I've got a solution that uses a while loop but I have a feeling that there is a better way to do this!

I will use a simplified dataset as an example as I don't want to swamp you with too much detail about my real table structures etc...

I have the following tables (plus some sample data) :


insert into dbo.VehicleModel
select 1, 'RT 100', NULL union all
select 2, 'TGB ACKROS TEC', NULL union all
select 3, 'TB 50 PREDATOR 2', NULL union all
select 4, 'TGB 409 CRUISE 50', NULL union all
select 5, 'ESCOR SUPER SONIC 400SL', NULL union all
select 6, 'SUPER SONIC 150 XI', NULL union all
select 7, 'RT X 350 SERIES', NULL union all
select 8, 'TB SALON 4000', NULL


create table dbo.VehicleModelNorm (
VehicleModelNormIdint
,VehicleModelNormvarchar(100)
)

insert into dbo.VehicleModelNorm
select 1, 'ACE' union all
select 2, 'TGB ACKROS' union all
select 3, 'SUPER SONIC' union all
select 4, 'TB 50' union all
select 5, 'RT' union all
select 6, 'TGB' union all
select 7, 'RT' union all
select 8, 'TB SALON'


What I need to do is update the VehicleModelNorm column in VehicleModel with the relevant ID from the VehicleModelNorm table. Sounds easy enough BUT here is the catch. In my vehicles table the VehicleModel are the actual vehicle model names. In my vehicleNorm table I have a more generalized name. What I want to do is look at each VehicleModel, try to find a match in the VehicleModelNorm table, if there is no match then chop off the last word and look again.

For example, starting with 'RT 100 7 SERIES', I want to try to find an exact match for that in my vehicleModelNorm table. If there is no match, remove the last word and look for 'RT 100 7' then look for 'RT 100' etc...

How can I do this in a set based solution? I can do this with a WHILE loop but I have a feeling it might be possible to do this with a CTE but I just can't get my head round it.

Your advice would be much appreciated!