CODEWITHSUNDEEP
mysqlsql
StudioTime
StudioTime

Reputation: 23989

Use LIKE with CONCAT

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?

Upvotes: 0

Views: 683

Answers (2)

fthiella
fthiella

Reputation: 49049

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

Upvotes: 3

kaushik0033
kaushik0033

Reputation: 677

Try this :-

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

Upvotes: 2

Related Questions