CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresfull-text-search
Dofs
Dofs

Reputation: 19317

SQLServer Stored Procedure

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?

Upvotes: 0

Views: 270

Answers (4)

Iralda Mitro
Iralda Mitro

Reputation: 7366

@CarDefintion = "Ford Mustang 2.5T"

Select top 1 CarDefinitionId 
  from dbo.CarModelDefinition 
 where @Cardefinition LIKE Model OR @Cardefinition LIKE Company

Upvotes: 1

Chad Grant
Chad Grant

Reputation: 45422

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 )

Upvotes: 0

tpdi
tpdi

Reputation: 35181

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.

Upvotes: 1

Ian Roke
Ian Roke

Reputation: 1784

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.

Upvotes: 0

Related Questions