views:

148

answers:

4

Hi, I would like to hear if any of you have an idea to do the following: I have one table containing some cardefinitions [id][Company][Mode], this could e.g. be Audi 100, Ford Mustan etc. I then have another field which is a string containing both the car and the model, this could be "Ford Mustang 2.5T", I then need to find the correct ID from the cardefinition. I though of doing

@CarDefintion = "Ford Mustang 2.5T"
Select top 1 CarDefinitionId 
  from dbo.CarModelDefinition 
 where CONTAINS(Model,@Cardefinition) 
   AND Contains(Company, @Cardefinition)

But since Model + Company is not unique I get this error: "Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'dbo.CarModelDefinition' because it is not full-text indexed." Does anyone have an idea of how I can solve this?

A: 
DECLARE @CarDefintion VARCHAR(25)
        SET @CarDefintion = 'Ford Mustang 2.5T'

DECLARE @Make  VARCHAR(25)
DECLARE @Model VARCHAR(25)
SELECT @Make  = SUBSTRING(@CarDefintion,0,CHARINDEX(' ',REVERSE(@CarDefintion)))
SELECT @Model = LTRIM( REPLACE(@CarDefintion,@Make,'')) 

--SELECT @Make,@Model

        SELECT TOP 1 * 
          FROM dbo.CarModelDefinition 
         WHERE Model   = @Model
           AND Company = @Make

CONTAINS() functions are for full text search, which you most likely are not using ( hence the errors )

Chad Grant
The problem is that I have the string "Ford Mustang 2.5T" and I don't know what the model and company is, therefore I can't use a regular where statement. I need to find the model and company from the string using the table CarDefinitions, which contains all the car models and companies (as written in the question).
Dofs
A: 

Must @CarDefintion be a string? Would it be possible to make it an array of Model and Company which you could then reference in your SQL?

Failing that would you be absolutely sure that someone doesn't just put "Mustang 2.5T" instead of "Ford Mustang 2.5T"? If you can be absolutely sure then one way to do it is to split by spaces then do a series of lookups on each segment to try and locate it.

Ian Roke
+1  A: 
@CarDefintion = "Ford Mustang 2.5T"

Select top 1 CarDefinitionId 
  from dbo.CarModelDefinition 
 where @Cardefinition LIKE Model OR @Cardefinition LIKE Company
DaDa
Test this, and you'll see it doesn't work for any reasonable data.
tpdi
+1  A: 

Search the string with the wildcarded columns values using LIKE.

@CarDefintion = "Ford Mustang 2.5T"
Select top 1 CarDefinitionId 
  from dbo.CarModelDefinition 
 where @Cardefinition like '%' + model + '%'
   AND @Cardefinition like '%' + company + '%';

+ is the string catenation operator.

tpdi
This was a really good idea, the problem is that it is returning a 'Ford A', is it possible that it should look at words and not chars, so it won't look for a in Mustang, but rather just the word Mustang?
Dofs
It worked when I added a space sign before the model e.g. Select top 1 CarModelDefinition.CarModelDefinitionId from dbo.CarModelDefinition where 'Ford Mustang 2.5T' like '% ' + CarModelDefinition.Model + '%' AND 'Ford Mustang 2.5T' like '%' + CarModelDefinition.Company + '%'
Dofs
The adding a space will work if every definition is in the form 'Company Model'. I'm not sure why you had to add the space though (I'd have to see the rows in CarModelDefinition), so test this thoroughly.
tpdi
This is because without the space it will just look for e.g. an 'A' which is in either Mustang or the model called 'A'. By adding the space it will look for a combination of the string and the space before, and therefore you are sure that you are getting the whole word and not a letter in your search word - I hope this ain't too much of gibberish, but thanks a lot.
Dofs
Ah, good point. And glad to be of help.
tpdi