mysql substr count,MySQL本机类似于PHP substr_count（）

Is there any function in MySQL that is similar to function substr_count() of PHP?

Actually I need to know how many items are in a set (eg. 1,2,3 is 3 items).

In a search, I found nothing that was native to this. So I made a workaround with the methods LENGTH() and REPLACE().

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

But it fail set is empty.

But I can solve that with a simple IF().

So, I'm searching for some more native, like, for instance:

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

Or, better yet:

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

Some solution guys?

Edit

Because of some doubts, I'll try to set some examples:

1,2,3 has 3 items: 1, 2 and 3;

100,200 has 2 items: 100 and 200;

1,2,4,9,16,25 has 6 items: 1, 2, 4, 9, 16 and 25;

Basically, I want the number that is the number of commas + 1. I've got this value, but I was wondering if there is a native way to do this, or a less costly than I did.

解决方案

There is no any native function for that and what you do is OK. But to alleviate some pain and hide complexity in your queries you can create your on function that takes care of NULLs, empty strings etc.

Something like this

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;

Sample output:

| ID | NUMBER_OF_VALUES |

|----|------------------|

| 1 | 3 |

| 2 | 2 |

| 3 | 6 |

| 4 | 0 |

| 5 | 0 |

Here is