Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    7

    Question Unanswered: Using 'LIKE' and 'IN' clause in same condition check

    Hi,
    Am trying to implement a search procedure that takes a keyword and does searches like a search engine does. It must get values corresponding to "LIKE" statement and "IN" statement. I would like to know whether there is a way of using them together, something like:

    SELECT * from table_hit where hit_field1 LIKE IN (@key) OR hit_field LIKE IN (@key)

    I am using MS SQL Server 2005
    Microsoft SQL Server Management Studio 9.00.1399.00




  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, you cannot do LIKE IN

    what were you thinking this would do? can you give an example?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Rudy is right, you cannot combine LIKE and IN. However, you can mimic this kind of behaviour like so:
    Code:
    --Mimic your table to search
    DECLARE @table_hit table (
       hit_field1 varchar(10)
    )
    
    INSERT INTO @table_hit (hit_field1) VALUES ('Rudy')
    INSERT INTO @table_hit (hit_field1) VALUES ('George')
    INSERT INTO @table_hit (hit_field1) VALUES ('Pootle')
    INSERT INTO @table_hit (hit_field1) VALUES ('Pat')
    INSERT INTO @table_hit (hit_field1) VALUES ('Sean')
    INSERT INTO @table_hit (hit_field1) VALUES ('Paul')
    INSERT INTO @table_hit (hit_field1) VALUES ('Brett')
    
    
    --Table variable to store search values
    DECLARE @keys table (
       value varchar(10)
    )
    
    INSERT INTO @keys (value) VALUES ('p%')
    INSERT INTO @keys (value) VALUES ('%t')
    
    SELECT t.hit_field1
    FROM   @table_hit As t
     INNER
      JOIN @keys As k
        ON t.hit_field1 LIKE k.value
    George
    Home | Blog

  4. #4
    Join Date
    Sep 2009
    Posts
    44
    Quote Originally Posted by manupkrishnsn View Post
    Am trying to implement a search procedure that takes a keyword and does searches like a search engine does.
    You need a full text search engine. I know 2008 has that functionality, so your best bet is probably to upgrade.

    Generally, it is possible to use a separate system like Lucene to run full text indexing, however, this requires writing code to keep the index up to date wrt the DBMS. If you can get it built in, that's a much better system.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •