#1
  1. I lov C in AIX/Linux, hate C++
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jul 2003
    Location
    Jacksonville, Florida
    Posts
    1,655
    Rep Power
    34

    Search Like Pattern for matching word in company name??


    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...
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,279
    Rep Power
    4279
    please give the actual table and column names for both tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. I lov C in AIX/Linux, hate C++
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jul 2003
    Location
    Jacksonville, Florida
    Posts
    1,655
    Rep Power
    34
    Someone had helped me out? So, here's the code that may help somebody here.

    Code:
    Declare @DealershipName Table(Name VarChar(50))
    Insert into @DealershipName Values('AudioPower of Georgia')
    Insert into @DealershipName Values('AutoWest Chrysler Jeep Dodge')
    Insert Into @DealershipName Values('Desert Buick GMC')
    Insert Into @DealershipName Values('George Moore Chevrolet')
    Insert Into @DealershipName Values('Wheel City Motor')
     
    Declare @ManufacturerMake Table(Make Varchar(20))
    Insert Into @ManufacturerMake Values('Audi')
    Insert Into @ManufacturerMake Values('Buick')
    Insert Into @ManufacturerMake Values('Chevrolet')
    Insert Into @ManufacturerMake Values('Chrysler')
    Insert Into @ManufacturerMake Values('Dodge')
    Insert Into @ManufacturerMake Values('Geo')
    Insert Into @ManufacturerMake Values('GMC')
    Insert Into @ManufacturerMake Values('Jeep')
     
    Select *
    From   @DealershipName As D
    Inner Join @ManufacturerMake As M 
    On ' ' + D.Name + ' ' Like '%[ ]' + M.Make + '[ ]%'
    ---------------------------------------------------------Name | Make
    ---------------------------------------------------------
    AutoWest Chrysler Jeep Dodge | Chrysler
    AutoWest Chrysler Jeep Dodge | Dodge
    AutoWest Chrysler Jeep Dodge | Jeep
    Desert Buick GMC | Buick
    Desert Buick GMC | GMC
    George Moore Chevrolet | Chevrolet
    [/Code]

    Thanks...

IMN logo majestic logo threadwatch logo seochat tools logo