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

Here's your help: fix your table design and split those |-separated chars into a proper child table. And no, we're not here to do your job for you. Try and write the query yourself, and we'll try to help fix it. — Marc B, May 27 '13 at 14:51
What is you question? Using `LIKE` what do you want to search? — Prahalad Gaggar, May 27 '13 at 14:53

score 4 · Accepted Answer · answered May 27 '13 at 15:05

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.

score 0 · Answer 2 · answered May 27 '13 at 15:00

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).

mysql SELECT WHERE string contains characters

2 Answers2

Linked