在 MySQL SELECT 中使用 REPLACE()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14069109/
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
Using REPLACE() in MySQL SELECT
提问by Jon K.
I apologise in advance for asking what I'm sure will prove to be a very simple question.
我提前道歉，因为我肯定会提出一个非常简单的问题。
I have a MySQL (5.5) database that includes, amongst other things, a field for telephone numbers. I'm trying to create a statement that will search that field, stripping out any spaces. So searching for '0208' will return '020 8', '02 08', '0 208', ' 0208', etc.
我有一个 MySQL (5.5) 数据库，其中包括一个电话号码字段。我正在尝试创建一个语句来搜索该字段，去除任何空格。因此，搜索“0208”将返回“020 8”、“02 08”、“0 208”、“0208”等。
And this is in Delphi XE2, in case that makes a difference.
这在 Delphi XE2 中，以防万一。
'SELECT * FROM sales_ledger WHERE REPLACE(telephone, " ", "") LIKE "%' + SearchEdit.Text + '%"'
...gives me an error...
...给我一个错误...
Invalid filter in WHERE clause
WHERE 子句中的过滤器无效
...and...
...和...
'SELECT REPLACE(telephone, " ", "") FROM sales_ledger WHERE REPLACE(telephone, " ", "") LIKE "%' + SearchEdit.Text + '%"'
...gives me...
...给我...
Invalid field name. General SQL error. Column not found.
无效的字段名称。一般 SQL 错误。未找到列。
...and I do actually need all fields returned anyway.
...我实际上确实需要返回所有字段。
May I ask for some assistance in correcting the syntax please. If you need more information, don't hesitate to ask. Thanks very much for your time.
我可以请求一些帮助来纠正语法吗？如果您需要更多信息，请随时询问。非常感谢你花时间陪伴。
EDIT: One potentially critical piece of information I missed out. The table is actually a Sage database that I'm accessing through ODBC. As nothing I try is working that may well be the root problem. Apologies for not saying that earlier.
编辑：我错过了一条潜在的关键信息。该表实际上是我通过 ODBC 访问的 Sage 数据库。由于我尝试的任何方法都不起作用，这很可能是根本问题。抱歉没有早点说。
回答by Champ
Your Query Seems Working Fine see the demo
您的查询似乎工作正常，请参阅演示
First try this query to you DB Client
首先向您的数据库客户端尝试此查询
SELECT * FROM sales_ledger
WHERE REPLACE(telephone, " ", "") LIKE "%0208%"
EDIT:
编辑：
if you query is still not working. fellow step by step process.
如果您的查询仍然无效。老乡一步一步的过程。
- try to run a simple select query (
SELECT * FROM sales_ledger) - if the first query run try adding simple where condition. so step by step you can make your query similar to original one and find where is the actual error from.
- 尝试运行一个简单的选择查询 (
SELECT * FROM sales_ledger) - 如果第一个查询运行尝试添加简单的 where 条件。所以一步一步地，你可以让你的查询类似于原始查询，并找出实际错误的来源。
回答by Sashi Kant
Try this ::
尝试这个 ：：
SELECT
REPLACE(telephone,' ', '') as replacedColumn
FROM sales_ledger
WHERE REPLACE(telephone,' ', '') LIKE '%"+ SearchEdit.Text +"%'"
OR
或者
Select temp1.*
from
(
SELECT
REPLACE(telephone,' ', '') as replacedColumn
FROM sales_ledger
) as temp1
WHERE temp1.replacedColumn LIKE '%"+SearchEdit.Text+"%'"
