Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using REPLACE() in MySQL SELECT

Tags:

mysql

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.

like image 402
Jon K. Avatar asked Dec 28 '12 11:12

Jon K.


People also ask

How does replace into work in MySQL?

REPLACE works exactly like INSERT , except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

How do I replace one column with another in MySQL?

To replace, use the REPLACE() MySQL function. Since you need to update the table for this, use the UPDATE() function with the SET clause.

Does insert () replace?

You can use the INSERT OR REPLACE statement to write new rows or replace existing rows in the table. The syntax and behavior of the INSERT OR REPLACE statement is similar to the INSERT statement. Unlike the INSERT statement, the INSERT OR REPLACE statement does not generate an error if a row already exists.


2 Answers

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.
like image 64
GajendraSinghParihar Avatar answered Oct 14 '22 00:10

GajendraSinghParihar


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+"%'"
like image 30
Sashi Kant Avatar answered Oct 14 '22 01:10

Sashi Kant