Is there any way to combine LIKE and IN in SQL?

Question

Right now I have the following SQL:

select MAX(score) as score, title from 
(
select 2 as score, title from tableName WHERE title LIKE '%railway employee%'
union 
select 1 as score, title from tableName WHERE title LIKE '%railway%'
union 
select 1 as score, title from tableName WHERE title LIKE '%employee%'
) as t1
group by title
order by score DESC

I would love to be able to do something like:

select MAX(score) as score, title from 
(
select LEN(CurrentTerm) as score, title from tableName WHERE title LIKE IN ('%railway employee%', '%railway%', '%employee%')
) as t1
group by title
order by score DESC

The CurrentTerm would be the matched term, not a column in table. Is there anything even remotely similar in SQL, specifically MySQL?

I guess you could use regular expressions instead of `LIKE IN`. — Andrew Logvinov, Jun 20 '13 at 21:16
Based on the query, it appears there is a lack of normalcy in the database. Better database structure may be the best solution. — Michael O'Neill, Jun 20 '13 at 21:20

Taryn · Accepted Answer · 2013-06-20T21:36:06.747

4

You cannot use LIKE IN but you can use OR:

select MAX(score) as score, title from 
(
  select LEN(CurrentTerm) as score, title 
  from tableName 
  WHERE title LIKE '%railway employee%'
    OR title LIKE '%railway%'
    OR title LIKE '%employee%'
) as t1
group by title
order by score DESC;

You might be able to use something similar to the following which uses a derived table of the 3 search terms with a score value:

select max(score) as score, title
from
(
  select 2 score, 'railway employee' term union all
  select 1 score, 'railway' term union all
  select 1 score, 'employee' term 
) d
inner join tableName t
  on title like concat('%', term, '%') 
group by title
order by score desc;

See SQL Fiddle with Demo

edited Jun 20 '13 at 21:36

answered Jun 20 '13 at 21:16

Taryn

242,637
56
362
405

1

`LEN(CurrentTerm)` is supposed to be based on the term that matched I think. So ones matching `railway employee` get scored higher than those matching just `railway` – Martin Smith Jun 20 '13 at 21:19
@MartinSmith Possibly, it would be helpful it the OP clarified. :) – Taryn Jun 20 '13 at 21:24
@bluefeet - sorry. Martin is correct in what I meant. I've updated the question to specify `CurrentTerm` it the matching term. – Justin808 Jun 20 '13 at 21:33
@Justin808 See my edit, I added another version that might solve your issue, even included a demo – Taryn Jun 20 '13 at 21:36

Gordon Linoff · Answer 2 · 2013-06-20T21:48:15.050

You could simplify your query by using or:

select MAX(score) as score, title
from (select LEN(CurrentTerm) as score, title
      from tableName
      WHERE title LIKE '%railway employee%' or
            title like '%railway%' or
            title like '%employee%'
      ) as t1
group by title
order by score DESC

EDIT:

I see, you don't have "CurrentTerm" in the database. Here is a better version:

select max(case when title LIKE '%railway employee%' then 2
                when title LIKE '%railway%' then 1
                when title LIKE '%employee%' then 1
           end) as score, title
from tableName
WHERE title like '%railway%' or title like '%employee%'
group by title
order by score DESC

The final where is actually not needed at all, but there for consistency with your original query. It doesn't need "%railway employee%" because that matches both.

Is there any way to combine LIKE and IN in SQL?

2 Answers2