MySQL WHERE 子句中带有 substr 的 SELECT 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9628460/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT Statement with substr in WHERE Clause
提问by John Woo
I have here sample table with three fields.
我这里有包含三个字段的示例表。
TableA
FieldA FieldB FieldC
======================================
123XYZ456 XYZ John
124NNN333 NNN Jenny
232XPT124 XPT Jade
456XXX345 XXX James
FieldAhas a fixedlength of 9. I didn't design this table and some applications are already using it.
FieldA有一个固定长度 9。我没有设计这个表，一些应用程序已经在使用它。
I want to select FieldBand FieldCwith conditions against FieldA.
我想选择FieldB并FieldC有条件反对FieldA。
Using this sql statement:
使用这个sql语句：
SELECT FieldB,
FieldC
FROM TableA
WHERE FieldA LIKE Concat(@paramA, '%', @paramB)
I cannot achieve my desired result. When I try to search with paramA value of 12and paramB value of '', I get the 2results:
我无法达到我想要的结果。当我尝试使用 paramA 值12和 paramB 值进行搜索时''，我得到了2结果：
FieldA FieldB FieldC
======================================
123XXX456 XXX John
124XXX333 XXX Jenny
because obviously it matches with 12%and that is not what I want. I want that the params should match the correct index of the string.
因为显然它匹配，12%这不是我想要的。我希望参数应该与字符串的正确索引匹配。
If I search for paramA = '12'and paramB = ''then it should have no result. To get the fields (FieldB, FieldC), I need the correct values of paramA = '123'and paramB = '456'so it will return XYZand John. If I want to return Jamesthen i have to give paramA = '456'and paramB = '345'
如果我搜索paramA = '12'和paramB = ''那么它应该没有结果。要获取字段 ( FieldB, FieldC)，我需要paramA = '123'and的正确值，paramB = '456'因此它将返回XYZand John。如果我想回来，James那么我必须付出paramA = '456'和paramB = '345'
How could I correctly build a SQL Statement for this? Any idea? Thanks.
我如何正确地为此构建 SQL 语句？任何的想法？谢谢。
回答by mechanical_meat
回答by Teja
SELECT Field2,Field3
FROM TABLE
WHERE SUBSTR(Field1,1,3)='123' AND SUBSTR(Field1,7,3)='456'
回答by SpliFF
You haven't made it clear why you're even allowing paramB to be blank in your input validation but to get the exact behaviour you have described you could just check for that case, ie:
您还没有说清楚为什么在输入验证中甚至允许 paramB 为空，但要获得您所描述的确切行为，您可以检查这种情况，即：
WHERE @paramA!='' AND @paramB!='' AND FieldA LIKE Concat(@paramA, '%', @paramB)
回答by Sam Adah
Like so: e.g classification table: Homes in America, Lands in America etc..
像这样：例如分类表：美国的房屋，美国的土地等。
mysql_query("select classification from properties where LEFT(classification, 5) = 'Homes'");
Result: Homes
结果：房屋
