Get exact match count in mysql text field

Question

I have a MySql table with a text field.

The text field will be populated with a random sequence similar to the following (can be any integer value):

14,4,24,20,34,2

34,67,4,98,64

No leading or trailing commas in strings (text field values).

For example, I want to count the total occurrences of only '4'.

Querying these two rows should return 2 instead of 7.

Not sure how to write a query like this.

Thanks

Answer 1

For MySQL 8.X

You can use REGEXP_REPLACE to find the 4, remove them from the string and calculate the difference in length:

SELECT
    LENGTH(vals) - LENGTH(REGEXP_REPLACE(vals, '(?<=^|,)4(?=$|,)', ''))
FROM
    T;

This will return the number of "4"s in each row, and you can then add them all up:

SELECT
    SUM(LENGTH(vals) - LENGTH(REGEXP_REPLACE(vals, '(?<=^|,)4(?=$|,)', '')))
FROM
    T;

You may also want to use AS to rename these values.

Explanation of regular expressions

(?<=^|,)4(?=$|,) is looking for "4" that meets the following conditions:

• Before comma or start of string
• After comma or end of string

For older versions of MySQL

The query is very ugly, but you can use this:

SELECT
    vals,
    LENGTH(Replace(Replace(Concat(',', vals, ','), ',4,', ',_,'), ',4,', ',_,')) - LENGTH(Replace(Replace(Replace(Concat(',', vals, ','), ',4,', ',_,'), ',4,', ',_,'), "_", "")) AS NB4
FROM
    test_table;

It first replaces all occurrences of "4" with underscores (_) (e.g. does not replace 4 in 54). It then calculates the length of the string with those underscores minus the length of the string without underscores, which is the number of "4"s in your list.

Why use so many REPLACE?

While testing the query, I discovered that MySQL's REPLACE function behaved differently than we expected. Taking this example: 4,4,4,4,4, if a single Replace is used, we expect it to return _,_,_,_,_. However, it replaces the comma in place, and if the comma "matches" twice, it doesn't count the second time, which is why the 2 REPLACE(..., ",4,", ",_, ")s reason.