Question 162134 in Mysql Question posted in
The official MySQL documentation can be found here.

“Query all rows in MySQL with a certain text in a column?

I am trying to query a table to get all the rows, but also return which ones match a string search.

For example, here is what the table would look like.

id title
1 foo
2 bar
3 foobar
4 moo
5 cow

And I want to query and indicate which ones contain "foo". The query result might look like the below. The ‘containsString’ could be 0|1 or null|id, just something to indicate that the row contains the string "foo".

id title containsString
1 foo 1
2 bar 0
3 foobar 1
4 moo 0
5 cow 0

The backup strategy is to query for all results and then programmatically Map over it. But I am hoping to do it in SQL.

Here is my first stab at it, but didn’t work because it only returned the rows that contained ‘foo’

SELECT t1.id, t1.title, t2.id as containsString 
FROM `news` t1, `news` t2
where t1.id = t2.id
LEFT JOIN news t2 ON 
WHERE t2.title LIKE '%foo%'
Tags:

2

Answers


  1. 0

    You can use CASE WHEN like:

    SELECT id, title,
CASE
    WHEN title like '%foo%' THEN 1
    
    ELSE 0
END as comtainsString
FROM news;
    Login or Signup to reply.
  2. 0

    Chetan method in this case works, but another method is to first make a table with all the have a foo in their name, and union it with the others:

    select * 
from
    (select *, 1 as containsFoo
     from table
     where title like "%foo%") as T1
    union
    (select *, 0 as containsFoo
     from table
     where title not like "%foo%")
  order by id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search