mysql SELECT WHERE string contains characters

Question

I have a table field events which can contains:

• Only a sequence of number, for example: 45
• or a sequence of number divided by the symbol |, for example 55|65|76

I think i have to use LIKE, but i don't know how. Can you help me?

Thanks

Answer 1

I would recommend using CONCAT to add a pipe | before and after your field, and then using a LIKE search. Something like this:

SELECT *
FROM YourTable 
WHERE concat('|',field,'|') like '%|45|%'

• SQL FIddle Demo

However, I highly recommend trying to normalize your data. Considering storing these in separate rows which would make searching/maintaining much easier.

Answer 2

To fix your query, use:

Select * from tbl where events='45' or events like '%|45' or events like '%|45|%' or   
events like '45|%'

but this is terribly slow and should not be used.

Instead, do as Marc B states and create a child table events (ID, event).