MySQL 查询字段内出现的情况

Question

我在名为 b_orders 的表中有一个名为 Invoice_notes 的字段。在此字段中，“信用卡被拒绝”字样将出现多次，具体取决于该卡被拒绝的次数。我正在尝试编写一个查询来仅选择短语“Credit Card Declined”出现次数少于 4 次的记录？

像这样的东西： SELECT * FROM b_orders 其中invoice_notes 具有“信用卡被拒绝”< 4 "

如有任何帮助，我们将不胜感激。 谢谢

Answer 1

这听起来像答案： http://pisceansheart.wordpress.com/2008/04/15/count-occurrence-of-character-in-a-string-using-mysql/
引用：

1. Counting the number of characters in the original string
2. Temporarily ‘deleting’ the character you want to count and count the string again
3. Subtract the first count from the second to get the number of occurrences

    SELECT LENGTH('foobarfoobarfoobar') - LENGTH(REPLACE('foobarfoobarfoobar', 'b', '')) AS occurrences  

对于您的具体示例：

SELECT * FROM b_orders where (length(invoice_notes) - length(replace(invoice_notes, 'Credit Card Declined'))) < (length('Credit Card Declined') * 4)

Answer 2

怎么样：

SELECT * FROM b_orders 
WHERE invoice_notes LIKE "%Credit Card Declined%" 
   AND invoice_notes NOT LIKE "%Credit Card Declined%Credit Card Declined%Credit Card Declined%Credit Card Declined%"

Answer 3

SELECT * FROM b_orders WHERE invoice_notes LIKE '%Credit Card Declined%' 
AND invoice_notes NOT LIKE REPEAT('%Credit Card Declined%',4);