Using REPLACE() in MySQL SELECT

Question

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.

And this is in Delphi XE2, in case that makes a difference.

'SELECT * FROM sales_ledger WHERE REPLACE(telephone, " ", "") LIKE "%' + SearchEdit.Text + '%"'

...gives me an error...

Invalid filter in WHERE clause

...and...

'SELECT REPLACE(telephone, " ", "") FROM sales_ledger WHERE REPLACE(telephone, " ", "") LIKE "%' + SearchEdit.Text + '%"'

...gives me...

Invalid field name. General SQL error. Column not found.

...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.

Answer 1

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.

Answer 2

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+"%'"