Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

Get exact match count in mysql text field

P粉493534105 2023-09-09 10:37:09

502

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

P粉493534105

reply all (1)

P粉8074716042023-09-10 00:31:48 1 floor

For MySQL 8.X

You can useREGEXP_REPLACEto 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 useASto 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'sREPLACEfunction 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 2REPLACE(..., ",4,", ",_, ")s reason.