Geek Answers Handbook

MySQL native is similar to PHP substr_count ()

Is there any function in MySQL that looks like the PHP substr_count() function? Actually, I need to know how many elements are in the set (for example, 1,2,3 - 3 elements).

In the search, I did not find anything that would be native to this. So I made a workaround with the LENGTH() and REPLACE() methods.

 SELECT LENGTH("1,2,3,4") - LENGTH(REPLACE("1,2,3,4", ",", "")) + 1;

But it is not installed, it is empty.
But I can solve this with a simple IF() .

So, I am looking for another native, for example, for example:

 SELECT SUBSTR_COUNT(",", "1,2,3,4") + 1;

Or, even better:

 SELECT LENGTH_OF_SET("1,2,3,4");

Are some guys solving?

Edit

Due to some doubt, I will try to give some examples:

  • 1,2,3 has 3 elements: 1, 2 and 3;
  • 100,200 has 2 elements: 100 and 200;
  • 1,2,4,9,16,25 has 6 elements: 1, 2, 4, 9, 16 and 25;

Basically, I want the number to be the number of commas + 1 . I have this value, but I was wondering if he has his own way of doing this or less expensive than me.

+2
source share
2 answers

There is no native function for this, and what you do is normal. But in order to alleviate some pain and hide complexity in your queries, you can create your own function that takes care of NULL s, empty lines, etc.

Something like that

 CREATE FUNCTION SUBSTR_COUNT ( _delimiter VARCHAR(12), _value VARCHAR(255) ) RETURNS INT RETURN COALESCE( CHAR_LENGTH(NULLIF(_value, '')) - CHAR_LENGTH(REPLACE(NULLIF(_value, ''), COALESCE(_delimiter, ','), '')) + 1, 0);

And then enjoy it

 SELECT id, SUBSTR_COUNT(',', value_set) number_of_values FROM table1; SELECT id, SUBSTR_COUNT(NULL, value_set) number_of_values FROM table1;

Output Example:

  |  ID |  NUMBER_OF_VALUES |
 | ---- | ------------------ |
 |  1 |  3 |
 |  2 |  2 |
 |  3 |  6 |
 |  4 |  0 |
 |  5 |  0 |

Here is the SQLFiddle demo

+2
source

It can do the trick.

 select char_length(REPLACE('1,2,3,4', ',', '')); or select char_length(REPLACE("1,2,3,4", ",", ""));

LENGTH () returns the length of the string, measured in bytes.

CHAR_LENGTH () returns the length of a string, measured in characters.

CHARACTER_LENGTH () is synonymous with CHAR_LENGTH ().

 select char_length(REPLACE('10,2000,3',',','')) from tableName LIMIT 1

Query works absolutely fine with char_length!

char length: 7 (even if with 0 the count is ok)

+1
source

More articles:


All Articles