MySQL本机类似于PHP substr_count() [英] MySQL native similar to PHP substr_count()

问题描述

MySQL中是否有类似于PHP函数substr_count()的函数? 实际上，我需要知道集合中有多少个项目(例如1,2,3是3个项目).

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).

在搜索中，我没有发现任何与生俱来的东西.因此，我使用方法LENGTH()和REPLACE()进行了解决.

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;

但是失败集为空.
但是我可以用一个简单的IF()来解决这个问题.

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;

或者，更好的是:

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

有解决方案的人吗?

修改

由于一些疑问，我将尝试举一些例子:

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

• 1,2,3具有3个项目:1、2和3；
• 100,200有2个项目:100和200；
• 1,2,4,9,16,25有6个项目:1、2、4、9、16和25；

• 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;

基本上，我想要的数字是逗号+ 1 .我有这个价值，但是我想知道是否有一种本机的方法可以做到，或者比我花费的成本更低.

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.

推荐答案

没有任何本机函数，您可以做.但是为了减轻查询的麻烦并隐藏查询的复杂性，您可以创建可以处理NULL s，空字符串等的on函数.

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.

类似这样的东西

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);

然后享受

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

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

示例输出:

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

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于MySQL本机类似于PHP substr_count()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！