SQL CONTAINTABLE with Multiple Word option - How to identify which word was found

• gjorgez

  SSC Journeyman

  Points: 83

  More actions

  May 5, 2017 at 11:11 am

  #402033

  Declare @color nvarchar (100) Set @color='Red or Blue'

  Select T1.CARDID,t1.CarDescription,@COLOR from containstable([dbo].[CARDDB],[file_stream],@Color) as t0 join [dbo].[cardDB] t1 on t0.=t1.[path_locator]

  The containtable provides the list of cars that are either red or blue. I need to to identify which car is red or blue by row.

  The desired results is:
  1 vin123 | honda blue 2 doors | blue
  2 vin456 | red gmc truck | Red

• crow1969

  Hall of Fame

  Points: 3001

  More actions

  May 5, 2017 at 11:50 am

  #1941359

  You can add a case statement that checks for blue or red.  Something like:

  
  case when t1.cardescription like '% blue %' then 'Blue'
       when t1.cardescription like '% red %' then 'Red'
       else 'N/A' end
     

  Very difficult to code, if the red or blue values are going to be input by users.

  This also assumes that the cars are in fact exclusively red or blue....unlike the car I drove in college.

• gjorgez

  SSC Journeyman

  Points: 83

  More actions

  May 5, 2017 at 12:33 pm

  #1941367

  Unfortunately , the  input comes from the user so colors will change..    🙂 Like you car in college   !!

  Thanks,

  J