Mysql查询使用通配符来搜索数据 [英] Mysql query using wildcards with like to search data

问题描述

我有一个表格计费，我需要搜索其中invoice_no（其列名称）应以a到e（a，b，c，d或e）之间的字母开头而不是e之外的数据。 br />


这里是我要执行的代码。

i have a table billing and there from i need to search data in which invoice_no(its column name)should begin with a letter between a to e( a,b,c,d or e) and not beyond e.

here is the code i am trying to execute.

select COALESCE(sum(payable_amount),0.00) from billing where bill_date = '2013/12/05' and pay_mode = 'Cash' AND invoice_no  like '[a-eA-E]%'

结算表包含的invoice_no数据也以a，b和c开头。但是这个查询返回零（0）。



代码编写是否有任何错误。 Plz帮助。

billing table has data having invoice_no that begins with a,b and c as well. But this query returns me zero(0).

Is there any mistake in code writing. Plz help.

推荐答案

MySQL中的LIKE运算符不是那么多功能我害怕。你可以提出多种选择：

The LIKE operator in MySQL is not that versatile I'm afraid. There are a number of alternatives you can sue though:

select COALESCE(sum(payable_amount),0.00) from billing where bill_date = '2013/12/05' and pay_mode = 'Cash' AND ASCII(invoice_no) in (65,66,67,68,69)

< br $> b $ b或

or

select COALESCE(sum(payable_amount),0.00) from billing where bill_date = '2013/12/05' and pay_mode = 'Cash' AND LEFT(invoice_no, 1) in ('A','B','C','D','E')

两者其中只会匹配大写字符 - 你会如果你想匹配小写，也需要添加相关的代码。



还有REGEXP函数，但是如果我能理解它就该死的让它运行​​起来,,,

http://dev.mysql.com /doc/refman/5.0/en/regexp.html#operator_regexp [ ^ ]

了解这一点，它将解决您当前的问题以及未来的问题。

< a href =http://dev.mysql.com/doc/refman/5.1/en/regexp.html>正则表达式 [ ^ ]

Learn this, it will solve your current problem and also future problem.
Regular Expression[^]

select COALESCE(sum(payable_amount),0.00) from billing where bill_date = '2013/12/05' and pay_mode = 'Cash' and invoice_no REGEXP '[abcde]';

这篇关于Mysql查询使用通配符来搜索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！