CODEWITHSUNDEEP
phpmysqlsql
Ridho
Ridho

Reputation: 23

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

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 )

Upvotes: 1

Views: 209

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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;

Upvotes: 0

jarlh
jarlh

Reputation: 44766

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

Upvotes: 3

Related Questions