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 (
	VehicleModelNormId		int
	,VehicleModelNorm		varchar(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'

Update vm
set VehicleModelNormId = vmn.VehicleModelNormId
from VehicleModel vm JOIN VehicleModelNorm vmn
on vm.VehicleModel like vmn.VehicleModelNorm + '%'

update a set VehicleModelNormId = b.VehicleModelNormId
from dbo.VehicleModel a inner join dbo.VehicleModelNorm b
  on ' ' + a.VehicleModel + ' ' LIKE '% ' + b.VehicleModelNorm + ' %'

quote:

---

also

---

if object_id('VehicleModel') > 0
drop table dbo.VehicleModel

create table dbo.VehicleModel (
	VehicleModelId		int
	,VehicleModel		varchar(100)
	,VehicleModelNormId	int
	)

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 union all
select 9, 'AERO 100', NULL union all
select 10 , 'AERO 50', NULL union all
select 11, 'A 170 ELEGANCE SE', NULL union all
select 12, 'A 150 CLASSIC SE', NULL union all
select 13, 'A 150 SPECIAL EDITION', NULL union all
select 14, 'GTV 2.0', NULL union all
select 15, 'GTV 600 XL', NULL


if object_id('VehicleModelNorm') > 0
drop table dbo.VehicleModelNorm

create table dbo.VehicleModelNorm (
	VehicleModelNormId		int
	,VehicleModelNorm		varchar(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' union all
select 9, 'A' union all
select 10, 'AERO' union all
select 11, 'GTV' union all
select 12, 'GT'

quote:

---

Originally posted by RyanRandall

So 'ESCOR SUPER SONIC 400SL' should map to nothing, yes? Using your algorithm...

ESCOR SUPER SONIC 400SL -> no
ESCOR SUPER SONIC -> no
ESCOR SUPER -> no
ESCOR -> no


Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

---

quote:

---

Originally posted by RyanRandall

Also, why do you have 'RT' twice in VehicleModelNorm?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

---

update a set VehicleModelNormId = (select top 1 VehicleModelNormId from dbo.VehicleModelNorm where
    ' ' + a.VehicleModel + ' ' LIKE ' ' + VehicleModelNorm + ' %' order by len(VehicleModelNorm) desc)
from dbo.VehicleModel a

/* Results
VehicleModelId VehicleModel                   VehicleModelNormId VehicleModelNorm
-------------- ------------------------------ ------------------ --------------------
1              RT 100                         7                  RT
2              TGB ACKROS TEC                 2                  TGB ACKROS
3              TB 50 PREDATOR 2               4                  TB 50
4              TGB 409 CRUISE 50              6                  TGB
5              ESCOR SUPER SONIC 400SL        NULL               NULL
6              SUPER SONIC 150 XI             3                  SUPER SONIC
7              RT X 350 SERIES                7                  RT
8              TB SALON 4000                  8                  TB SALON
9              AERO 100                       10                 AERO
10             AERO 50                        10                 AERO
11             A 170 ELEGANCE SE              9                  A
12             A 150 CLASSIC SE               9                  A
13             A 150 SPECIAL EDITION          9                  A
14             GTV 2.0                        11                 GTV
15             GTV 600 XL                     11                 GTV
*/

select * from dbo.VehicleModel a left outer join dbo.VehicleModelNorm b on
    ' ' + a.VehicleModel + ' ' LIKE ' ' + VehicleModelNorm + ' %'
order by VehicleModelId

select *, (select top 1 VehicleModelNormId from dbo.VehicleModelNorm where
    ' ' + a.VehicleModel + ' ' LIKE ' ' + VehicleModelNorm + ' %' order by len(VehicleModelNorm) desc)
from dbo.VehicleModel a

quote:

---

Originally posted by RyanRandall

<Group hug>

Re understanding it, it might help to understand the approach to solving it...

1. Join the tables together based on some matching criteria

select * from dbo.VehicleModel a left outer join dbo.VehicleModelNorm b on
    ' ' + a.VehicleModel + ' ' LIKE ' ' + VehicleModelNorm + ' %'
order by VehicleModelId

2. Figure out the criteria for which row to select - hence my question "What do you want to do if there is more than one VehicleModelNormId match?". As it turned out, you wanted the 'longest' match.

3. Modify the query to give you only this (using top 1 with a sort is a neat way to do this 'in one go')

select *, (select top 1 VehicleModelNormId from dbo.VehicleModelNorm where
    ' ' + a.VehicleModel + ' ' LIKE ' ' + VehicleModelNorm + ' %' order by len(VehicleModelNorm) desc)
from dbo.VehicleModel a

4. Modify this to create the final update statement


Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

---