带有 LIKE 运算符的 Select 语句中的 MySQL 案例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3579760/
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
MySQL Case in Select Statement with LIKE operator
提问by HurnsMobile
Is it possible to combine the CASEstatement and the LIKEoperator in a MySQL SELECTstatement?
是否可以在 MySQL语句中组合CASE语句和LIKE运算符SELECT？
For Example, I am trying to query a database that stores data in a single column in either one of two formats (this is awful and hurts my head, but I cant change the data so it is what it is.). So sometimes the column numberswould have data like "6901xxxxxxxx" and sometimes it would have data like "91xxxxxxxxxxx".
例如，我试图查询一个数据库，该数据库以两种格式中的任何一种格式将数据存储在单个列中（这很糟糕并且让我头疼，但我无法更改数据，所以它就是这样。）。因此，有时该列numbers会包含“6901xxxxxxxx”之类的数据，有时它会包含“91xxxxxxxxxxx”之类的数据。
What I would like to do is query the data like so -
我想做的是像这样查询数据 -
SELECT
CASE digits
WHEN LIKE "6901%" THEN substr(digits,4)
WHEN LIKE "91%" THEN substr(digits,2)
END as "digits",
FROM raw
This obviously doesn't work but Im hoping its possible.
这显然不起作用，但我希望它可能。
回答by Daniel Vandersluis
回答by Tim
Try
尝试
SELECT
CASE true
WHEN digits LIKE "6901%" THEN substr(digits,4)
WHEN digits LIKE "91%" THEN substr(digits,2)
END as "digits",
FROM raw
回答by p.campbell
Perhaps use LEFT()?
也许使用LEFT()？
SELECT
CASE
WHEN LEFT(digits, 4) = '6901' THEN substr(digits,4)
WHEN LEFT(digits, 2) = '91' THEN substr(digits,2)
END
FROM raw
Should be more performant than the LIKE.
应该比LIKE.
回答by Joshua Martell
Assuming the number of digits is constant, you could use something like this:
假设位数是常数，你可以使用这样的东西：
SELECT IF(digits > 919999, MOD(digits, 100), MOD(digits, 10000) FROM raw
Add 0's to match your actual number of digits.
添加0's 以匹配您的实际位数。
