Full text Search CONTAINS with Variables RSS

• • Reply
  

  porterboy

  Member

  442 Points

  185 Posts

  Full text Search CONTAINS with Variables

  Aug 09, 2010 07:06 PM|LINK

  Can someone show me the syntax for writing an SP with CONTAINS and variables

  This is the query I want

  Select *
  from Something
  Where Contains(FTField,'"mysearch*" AND "MySearch2*"')

  I Can't figure out how to Format with Variables

  DECLARE @String1 varchar(50)
  DECLARE @String2 varchar(50)

  SET @String1= "mysearch*"
  SET @String2 = "MySearch2*"

  Select *
  from Something
  Where Contains(FTField,@String1 AND @String2) ?? This doesn't work.

  Thanks in Advance

   

   

   

  • Moved by SGWellens on Aug 09, 2010 08:04 PM
• • Reply
  

  dmnida

  Member

  570 Points

  115 Posts

  Re: Full text Search CONTAINS with Variables

  Aug 09, 2010 08:55 PM|LINK

  Have you tried this:

  DECLARE @String1 varchar(50)
  DECLARE @String2 varchar(50)

  DECLARE @String3 varchar(120)

  SET @String1= 'mysearch*'
  SET @String2 = 'MySearch2*'

  SET @String3 = @String1 + ' AND ' + @String2

  Select *
  from Something
  Where Contains(FTField,@String3) 

• • Reply
  

  porterboy

  Member

  442 Points

  185 Posts

  Re: Full text Search CONTAINS with Variables

  Aug 10, 2010 02:29 PM|LINK

  That kind of works however it doesn't use the wildcard.  

  I have the same problem with even a single variable. for example.

  This only returns records with the word dog on it's own

  DECLARE @String1 varchar(50)
  SET @String1= 'dog*'
  Select *
  from Something
  Where Contains(FTField,@String1)

  However this returns all records with dog as a prefix (doghouse,dogbone) etc

  Select *
  from Something
  Where Contains(FTField,'"dog*"')
  

• • Reply
  

  dmnida

  Member

  570 Points

  115 Posts

  Re: Full text Search CONTAINS with Variables

  Aug 10, 2010 02:44 PM|LINK

  Try this:

  Have you tried this:

  DECLARE @String1 varchar(50)
  DECLARE @String2 varchar(50)

  DECLARE @String3 varchar(120)

  SET @String1= '"mysearch*"'
  SET @String2 = '"MySearch2*"'

  SET @String3 = @String1 + ' AND ' + @String2

  Select *
  from Something
  Where Contains(FTField,@String3) 

  • Marked as answer by porterboy on Aug 10, 2010 03:25 PM
• • Reply
  

  porterboy

  Member

  442 Points

  185 Posts

  Re: Full text Search CONTAINS with Variables

  Aug 10, 2010 03:25 PM|LINK

  That worked Thanks 

• • Reply
  

  seema_naz

  Member

  6 Points

  19 Posts

  Re: Full text Search CONTAINS with Variables

  Dec 11, 2012 06:15 AM|LINK

  hello dmnida,

  Iam having the same problem, but my searchtext come through input parameters into the sp.. so i don't know how to make that, here's my code:

  ALTER proc [dbo].[search](@SearchText nvarchar(255))
  as
   
  declare @st nvarchar(255)
  set @st= '*' + @SearchText + '*'
  
  select terms_en from terms
      where CONTAINS(terms_en, '@st')
          
          

  can you please tell what should i do to make this work? thanks :)