MySQL multithreading in line
I have a 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 has a value in each of the values.
So, if I want to count the times "A" and "B", the sql expression 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 "ACBB" and the characters I want to find are "A" and "B". And the result is 2. First we search for "A" and "B". When we replace the first "A" and "B", the value will be "CB" ("C" and "B") and the counter will be 1. If there is another "A" or "B", it will be the count. So the counter for the example is 2 because there is one "B". If there are 2 'B', the result is 3.
Is there a way to do this? I don't want to use php, vb, etc. Just MySQL
source to share
I tried this in Oracle, 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 must change it in your query.
source to share
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;
source to share