MySQL Query：限制子串数的结果

I have a table like this

我有一张这样的桌子

ROW ID         |   CONTENT
------------------------------------------------
test1          |   foo, foo, foo
test2          |   bar, bar
test3          |   foo, foo
test4          |   foo, foo, foo, foo

What I want to achieve is query that gives me the rows but limiting it respecting the occurrences of a substring.

我想要实现的是查询,它给我行,但限制它尊重子串的出现。

Some examples could be:

一些例子可能是:

• Limit result to 3 "foo" occurrences -> should return test1

将结果限制为3“foo”次数 - >应返回test1

• Limit result to 4 "foo" occurrences -> should return test1 and test3

将结果限制为4“foo”次数 - >应返回test1和test3

• Limit result to 100 "foo" occurrences -> should return test1,test3, test4

将结果限制为100“foo”次数 - >应返回test1,test3,test4

• Limit result to 7 "foo" occurrences -> should also return test1,test3, test4

将结果限制为7“foo”次数 - >还应返回test1,test3,test4

Is there any way to do this? Thanks in advance!

有没有办法做到这一点?提前致谢!

P.S. : I should have mentioned that the ',' can be any string without a predictable length.

附: :我应该提到','可以是没有可预测长度的任何字符串。

3 个解决方案

#1

SQL Fiddle

MySQL 5.5.32 Schema Setup:

MySQL 5.5.32架构设置:

CREATE TABLE Table1
    (`ROW ID` varchar(5), `CONTENT` varchar(18))
;

INSERT INTO Table1
    (`ROW ID`, `CONTENT`)
VALUES
    ('test1', 'foo, foo, foo'),
    ('test2', 'bar, bar'),
    ('test3', 'foo, foo'),
    ('test4', 'foo, foo, foo, foo')
;

Query 1:

SELECT *
FROM Table1
WHERE ((LENGTH(CONTENT) - 
        LENGTH(REPLACE(CONTENT, ',', ''))) + 1) < 3
     AND SUBSTRING(CONTENT,1,LENGTH('FOO')) = 'FOO'

Results:

| ROW ID |  CONTENT |
|--------|----------|
|  test3 | foo, foo |

EDIT :

If you are dealing with phrases, it could look like this :

如果你正在处理短语,它可能看起来像这样:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

MySQL 5.5.32架构设置:

CREATE TABLE Table1
    (`ROW ID` varchar(5), `CONTENT` varchar(48))
;

INSERT INTO Table1
    (`ROW ID`, `CONTENT`)
VALUES
    ('test1', 'foo de foo refe foo'),
    ('test2', 'bar re bar'),
    ('test3', 'foo rer ef foo'),
    ('test4', 'foo rer foo fsdfs foo dfsfe foo')
;

Query 1:

SELECT *
FROM Table1
WHERE (LENGTH(CONCAT(' ',CONTENT,' ')) - 
       LENGTH(REPLACE(CONCAT(' ',UPPER(CONTENT),' '), 
                      CONCAT(' ','FOO',' '), '')))
       /(LENGTH('FOO')+2) < 3 AND
       CONCAT(' ',CONTENT,' ') LIKE CONCAT('% ','FOO',' %')

Results:

| ROW ID |        CONTENT |
|--------|----------------|
|  test3 | foo rer ef foo |

#2

You want to count the number of foos in the list. This is pretty easy:

您想要计算列表中的foos数量。这很简单:

select t.*
from t
where (char_length(concat(', ', content, ', ')) -
       char_length(replace(concat(', ', content, ', '), ', foo, ', '1234567'))
      ) = 3;

The idea is to replace 'foo' with something that has one fewer character. However, you might want to be careful with 'foobars' and 'barfood' and other strings that could cause a false positive. So, this version just puts the separators at the beginning and end of the string.

我们的想法是将'foo'替换为少一个字符的东西。但是,您可能需要小心“foobars”和“barfood”以及其他可能导致误报的字符串。所以,这个版本只是将分隔符放在字符串的开头和结尾。

Once you have this information, you can do whatever comparisons you would like.

获得此信息后,您可以进行任何您想要的比较。

#3

MySQL unfortunately doesn't have any bulit-in function for what you want to do. You need something like SUBSTRING_COUNT, which doesn't exist. What you can do is, based on this answer` calculate that value.

遗憾的是,MySQL没有任何你想要做的功能。你需要像SUBSTRING_COUNT这样的东西,它不存在。你可以做的是,基于这个答案`计算这个价值。

Something like this might work:

这样的事情可能有用:

SELECT rowid,
(LENGTH(content) - LENGTH(REPLACE(content, 'foo', ''))) / LENGTH('foo') AS cnt

FROM thetable

HAVING cnt > 0 && cnt < 4;

DEMO: http://sqlfiddle.com/#!2/10599/7