MySQL multi string occurence in string

Question

I have table like this:

-----------
ID | Value
-----------
1  | AAAA
2  | ABCD
3  | AADC
4  | ABBD

I am trying to figure out how to return the number of times a string occurs in each of the Value.

So, if I want to count of time 'A' and 'B'appears, the sql statement will return like this:

    -------------------
    ID | Value | Count
    ------------------- 
    1  | AAAA  |   0 
    2  | ABCD  |   1 
    3  | AADC  |   0 
    4  | ABBD  |   2 
    5  | ABBB  |   3 
    6  | AABB  |   3 
    7  | AAAB  |   3
    8  | AABC  |   2
    9  | DBCA  |   1
   10  | CBAA  |   2
   11  | BDAB  |   2

Example: The value is "ACBB" and the chars I want to search is 'A' and 'B'. And the result is 2. First we search 'A' and 'B'. When we replace first 'A' and 'B', the value will be "CB" ('C' and 'B') and the counter will be 1. If there is one more 'A' or 'B', it will be count. So the counter for the example is 2, because there is one 'B'. If there is 2 'B', the result is 3.

Is there any way to do this? I do not want to use php, vb, etc. Just MySQL

First, the value must match 'A' and 'B'. And if there is other value 'A' or 'B' match then it will be counting. So the step is (A and B), (A or B), (A or B) = 3 — Slocky, Apr 29 '15 at 17:54
possible duplicate of [Count the number of occurences of a string in a VARCHAR field?](http://stackoverflow.com/questions/12344795/count-the-number-of-occurences-of-a-string-in-a-varchar-field) — Leonardo Herrera, Apr 29 '15 at 19:01
@Leandro . It is not the duplicate. Check the count of 6th row `AABB = 3`. . Its not simply counting occurrence of a particular character. Good logic is involved here . +1 for good question — mysqlrockstar, Apr 29 '15 at 19:11

Igorovics · Answer 1 · 2015-04-30T06:17:18.363

0

I tried this in Oracle, I hope it works in MySQL too:

select value,
  case when value not like '%A%' or value not like '%B%' THEN 0
  else (LENGTH(value) - LENGTH(REPLACE(value, 'A', '')) +  LENGTH(value) - LENGTH(REPLACE(value, 'B', '')) - 1)
  end case from test;

Of course if your table has a different name, you have to change it in your query.

edited Apr 30 '15 at 06:17

answered Apr 29 '15 at 18:33

Igorovics

416
1
7
25

A and B which I want to search is not a string ("AB"), but 2 chars 'A' and 'B'. And according your answer, when the value is AAAA, the counter is 3. The first rule is, the value must contains 2 chars ('A' and 'B') which I want to search, wherever its position. If it is does not contains one of char which I want to search, then the counter is zero – Slocky Apr 29 '15 at 19:03
Also we need to divide the resulting number by `needle length` as it doesn’t work correctly with needle string length greater than 1 character – mysqlrockstar Apr 29 '15 at 19:15
@Slocky : Sorry, in this case I misunderstood your question. The other part: AAAA gives back 0 in Oracle, that should be the same in MySQL. – Igorovics Apr 29 '15 at 19:25
I edited my answer a bit. The first case condition has been changed, now it makes sure, that whether A, or B is missing you got 0. I tested it inOracle, and gives exactly the values you wrote in your example above. Please try it in MySQL because I couldn't. – Igorovics Apr 30 '15 at 06:53

score 0 · Answer 2 · answered Apr 29 '15 at 18:45

0

SELECT ID, Value,    
  CASE 
   WHEN ROUND (( LENGTH(Value) - LENGTH( REPLACE ( Value, "AB", ""))) / LENGTH("AB")) = 0 THEN 0
   WHEN ROUND (( LENGTH(Value) - LENGTH( REPLACE ( Value, "AB", ""))) / LENGTH("AB")) = 1 THEN
   (ROUND (( LENGTH(Value) - LENGTH( REPLACE ( Value, "A", ""))) / LENGTH("A"))  +
   (ROUND (( LENGTH(Value) - LENGTH( REPLACE ( Value, "B", ""))) / LENGTH("B")) - 1
 END
AS Count    
FROM tablename;

answered Apr 29 '15 at 18:45

mysqlrockstar

2,536
1
19
36

every char position in value string can be random ("DACB", "BCDA", etc). It is not always "AB".. – Slocky Apr 30 '15 at 00:46

MySQL multi string occurence in string

2 Answers2