Question 206232 in Mysql Question posted in
The official MySQL documentation can be found here.

Get a count on an exact match from a text field in mysql

I have a MySql table with a text field.

The text field would be populated with a random sequence similar to this (could be any integer values though):

14,4,24,20,34,2

34,67,4,98,64

No leading or trailing comma on the string (text field value).

I would like to do a count on total occurrences of only ‘4’ for example.

Query on these two rows should return a count of 2 and not 7.

Not positive how a query like this would be formulated.

thanks

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Here is what I got working, and seems to give the correct result:

    select count(*) as total_blocks from poll_info where poll_blocks like '%,4,%' OR poll_blocks like '4,%' OR poll_blocks like '%,4'

    I will do some further testing and hopefully this will solve the issue, thanks for your time in this.


  2. 0

    For MySQL 8.X

    You can make use of REGEXP_REPLACE to find the 4, remove them from the string and compute the difference of length:

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

    That returns the number of "4" per row, then you can sum it all up:

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

    You might also want to rename these values with AS.

    Explanation of the Regex

    (?<=^|,)4(?=$|,) is looking for "4" which match these conditions:

    • There’s a comma or the beginning of the string before
    • There’s a comma or the end of the string after

    For older MySQL versions

    The query is quite ugly, but you can use the following:

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

    What it does is first replace all the occurrences of "4" as an element (so not in 54 for example) with underscores (_).
    Then, it computes the length of the string with these underscores minus the length of the string without the underscores, and that’s the number of "4"s in your list.

    Why so many REPLACE?

    During testing the query, I’ve discovered that MySQL REPLACE functions doesn’t behave as we would expect. With this example: 4,4,4,4,4, if using a single Replace, we would expect it to return _,_,_,_,_. However, it does the replace in-place, and if a comma is "matched" twice, it won’t count the second time, hence why 2 REPLACE(..., ",4,", ",_,").

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search