i have a simple (i hope its simple) question. In my database, i have an entry like this:
Now, i need a response with true or a count if the '514' in 'error_code' is in the string 'count_alarm'. In this example it returns zero because 514 isnt in count_alarm. I beginns the query, but i dont know how i can solve this query:
select count(*) from table where sID='56df32a1463d4387' and [if error_code in count_alarm then True]
Somebody an idea?
Perhaps you can just use REGEXP here:
SELECT COUNT(*) AS cnt
FROM yourTable
WHERE sID = '56df32a1463d4387' AND
count_alarm REGEXP CONCAT('[[:<:]]', error_code, '[[:>:]]');
find_in_set can parse comma separated fields:
select count(*)
from your_table
where sID = '56df32a1463d4387'
and find_in_set(error_code, replace(count_alarm, '|', ',')) > 0
or use instr
where sID = '56df32a1463d4387'
and instr(count_alarm, concat('|', error_code, '|')) > 0
)select count(sID) as n514 from table where sID='56df32a1463d4387'
and count_alarm like '%|514|%'
The LIKE operator searches for the pattern |514| anywhere in the value under count_alarm.
The assumption is that the first and last character of that value is this character | else 514 would not be found if it is the first or last pattern within that value.
