0

i have a simple (i hope its simple) question. In my database, i have an entry like this:

enter image description here

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?

griffinland
  • 33
  • 4
  • https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 – RiggsFolly Jul 06 '21 at 09:40
  • 2
    Normalize your schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes, it is.). – sticky bit Jul 06 '21 at 09:43
  • thanks for the hint. i´ll check that. – griffinland Jul 06 '21 at 10:23

3 Answers3

1

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
juergen d
  • 201,996
  • 37
  • 293
  • 362
1

Perhaps you can just use REGEXP here:

SELECT COUNT(*) AS cnt
FROM yourTable
WHERE sID = '56df32a1463d4387' AND
      count_alarm REGEXP CONCAT('[[:<:]]', error_code, '[[:>:]]');
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
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.

Heinz
  • 239
  • 1
  • 2
  • 8