Search Like Pattern for matching word in company name?? 1

[fletchsod]

It had gotten more complicated than I thought it on writing a sql-query script.

I have a "manufacturer's make" table that need to match against the "dealership's company name" table to get a matching hit.

Let's say I have "dealership's company name" table, example below.

--snip--
Dealership-Name
------------------------
George Moore Chevrolet
Wheel City Motor
Desert Buick GMC
--snip--

When it is search against the make, I would like to get this result, example below.

--snip--
Dealership-Name | Make
---------------------------------------
George Moore Chevrolet | Chevrolet
Desert Buick GMC | Buick
Desert Buick GMC | GMC
--snip--

I figure I can use the space to seperate the words (dealership-name) for matching against the make but I don't see how.

Can anyone show me how it can be done? Sample sql-query??

Thanks...

 

[djj55]

Do you plan on having a lookup table of makes?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

You can join the dealership table with the manufacturer make take based on a like criteria, something like this:

Declare @DealershipName Table(Name VarChar(50))

Insert Into @DealershipName Values('George Moore Chevrolet')
Insert Into @DealershipName Values('Wheel City Motor')
Insert Into @DealershipName Values('Desert Buick GMC')

Declare @ManufacturerMake Table(Make Varchar(20))

Insert Into @ManufacturerMake Values('Chevrolet')
Insert Into @ManufacturerMake Values('Buick')
Insert Into @ManufacturerMake Values('GMC')

Select *
From   @DealershipName As D
       Inner Join @ManufacturerMake As M
         On D.Name Like '%' + M.Make + '%'

Note that I hard coded some test data in to table variables so you can copy/paste the code to see how it works. Once you are satisfied that it returns the results you want, you can change the query to use your real tables instead.

Also note that this type of join (based on a like search) is likely to be a bit slow when you run it against large tables. Give it a try a let us know how it works for you.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fletchsod]

Hmm... Inner Join. Not bad, that will do. It's only an one time update. Thanks...

Also, w/ the "%" wildcard, what if it returned a false positive result. That is a very small change so I'm not gonna sweat it but I'm curious. I'm gonna save a copy of this for future reference.

For example...
--snip--
George AudioPower Buick
--snip--

When querying against the Make would return
--snip--
George AudioPower Buick | Audi
Geroge AudioPower Buick | Buick
--snip--

So, is there a better way against that??

 

[fletchsod]

P.S. - The space seperator is the key here...

 

[gmmastros]

I see what you mean... You could add a space to the dealer name (before and after) and then check for <space>make<space>, like this:

Declare @DealershipName Table(Name VarChar(50))

Insert Into @DealershipName Values('George Moore Chevrolet')
Insert Into @DealershipName Values('Wheel City Motor')
Insert Into @DealershipName Values('Desert Buick GMC')
Insert Into @DealershipName Values('George AudioPower Buick')


Declare @ManufacturerMake Table(Make Varchar(20))

Insert Into @ManufacturerMake Values('Chevrolet')
Insert Into @ManufacturerMake Values('Buick')
Insert Into @ManufacturerMake Values('GMC')
Insert Into @ManufacturerMake Values('Audi')

Select *
From   @DealershipName As D
       Inner Join @ManufacturerMake As M
         On [!]' ' +[/!] D.Name [!]+ ' '[/!] Like '%[!][ ][/!]' + M.Make + '[!][ ][/!]%'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I should mention that adding the space to the dealer name, both before then name and after allows us to get results that match against the first and/or last word in the dealer name.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fletchsod]

Oh wow! That's work great! You know the regular expression, I only can do the basic but not as advance as this one. Thanks a million! I'm gonna be saving this sql-query for future reference. Wow!