Use LIKE with CONCAT

Question

Can I use like with CONCAT? I cannot find anywhere that says otherwise. I'm trying the following and gives an error:

SELECT *, (select count(*) from acts where bookingType like '%CONCAT(bookingTypes_id, ',')%') as how_many FROM bookingTypes order by bookingType

NOTE: I am using CONCAT with comma because the bookingType field is basically a list of numbers comma separated e.g. 40,14,45 - and I can't just search for e.g. 4 as it would show when shouldn't - is there a better way to search within that field?

LIKE isn't designed to work like IN – Mitch Wheat Oct 29 '13 at 07:39 — Mitch Wheat, Oct 29 '13 at 07:39

fthiella · Accepted Answer · 2013-10-29T07:54:23.380

3

It is usually better not to store comma separated values in a field, but you can use CONCAT this way:

SELECT
  *,
  (SELECT COUNT(*)
   FROM   acts
   WHERE  CONCAT(',', bookingType, ',')
          LIKE CONCAT('%,', bookingTypes_id, ',%')) AS how_many
FROM bookingTypes
ORDER BY bookingType

or you can use FIND_IN_SET:

SELECT
  *,
  (SELECT COUNT(*)
   FROM   acts
   WHERE  FIND_IN_SET(bookingTypes_id, bookingType)>0) AS how_many
FROM bookingTypes
ORDER BY bookingType

edited Oct 29 '13 at 07:54

answered Oct 29 '13 at 07:43

fthiella

48,073
15
90
106

first option perfect, many thanks... slight error in code should be: ...LIKE CONCAT('%'... remove comma after % – StudioTime Oct 29 '13 at 07:51
@DarrenSweeney i fixed the first query, if I unterstand correctly it's better to use a concat also on bookingType, so the where clause will be `',40,14,45,' LIKE '%,4,%'` and it will match bookingTypes_id even if it's at the beginning or at the end of the string – fthiella Oct 29 '13 at 07:58
Yeah I agree but I'm finding flaws in this whole process I think, and wondering whether I should just have a separate table for act:bookingType relationship each on it's own line - when I add using implode the first and last don't have preceding or trailing commas which makes this query fall down – StudioTime Oct 29 '13 at 08:54
@DarrenSweeney yes queries like this can't be optimized and can't make use of an index, please see also this question http://stackoverflow.com/questions/10480568/comma-separated-values-in-mysql-in-clause it is usually better to insert each relationship on it's own line – fthiella Oct 29 '13 at 09:02

score 2 · Answer 2 · answered Oct 29 '13 at 07:45

2

Try this :-

SELECT *
FROM acts
WHERE bookingTypes LIKE CONCAT('%', 
(SELECT bookingTypes FROM bookingTypes order by bookingType LIMIT 1), '%');

answered Oct 29 '13 at 07:45

kaushik0033

679
6
12

Use LIKE with CONCAT

2 Answers2