MYSQL count how many times SOME value is exist as value in a field of table

Question

MYSQL Check If some value is exist as value in a field of table

I have table A with field :
id      name     value
1       item1      822
2       item2      658
3       item3      321

I'm trying to figure out this :
I want to count how many times values is exist in a field

example :
If I'm searching 2 and 8, then

The Result is : The count is 3 for row id 1 ( because it's has two 2 and one 8 )

And the count is 1 for row id 2 ( because it's has one 8 number )

And the count is 1 for row id 3 ( because it's has one 2 number )

the data type is varchar for value, dont care about the column name, its just example — Ridho, Jan 26 '17 at 14:43
Possible duplicate of [How to count instances of character in SQL Column](http://stackoverflow.com/questions/1860457/how-to-count-instances-of-character-in-sql-column) — Olaf Dietsche, Jan 26 '17 at 14:45
no it's different, I'm asking how to count 2 value, the link you give just count 1 value in a field — Ridho, Jan 26 '17 at 14:49

score 3 · Answer 1 · answered Jan 26 '17 at 14:45

3

select id, case when value like '%2%' then 1 else 0 end +
               case when value like '%8%' then 1 else 0 end
from tablename

answered Jan 26 '17 at 14:45

jarlh

42,561
8
45
63

score 0 · Accepted Answer · answered Jan 26 '17 at 14:47

0

You can do this with replace() and length() (or char_length()):

select (length(value) - length(replace(replace(value, '2', ''), '8', ''))) as num_occurrences
from t;

answered Jan 26 '17 at 14:47

Gordon Linoff

1,242,037
58
646
786

wait.. I think your answer is better @Gordon Linoff, because with your code, if value is 2828, it will count 4, nice – Ridho Jan 26 '17 at 15:01
@Ridho, you said you wanted count 2 for row id 1 (822), because it has an 8 and a 2. Have you changed your mind? – jarlh Jan 27 '17 at 08:06

MYSQL count how many times SOME value is exist as value in a field of table

2 Answers2