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.

Pradeep Kumar · Accepted Answer · 2015-02-19 07:42:01Z

10

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.

edited Feb 19, 2015 at 7:42

Pradeep Kumar

4,1712 gold badges36 silver badges41 bronze badges

answered Dec 28, 2012 at 12:03

GajendraSinghParihar

9,16911 gold badges40 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jon K. Over a year ago

Still not working Champ but I've edited the original post with more information that may make a difference. Thanks for answering!

Jon K. Over a year ago

SELECT * FROM sales_ledger WHERE telephone LIKE "%' + SearchEdit.Text + '%" - works. SELECT * FROM sales_ledger WHERE REPLACE(telephone, " ", "") LIKE "%' + SearchEdit.Text + '%" - does not work.

GajendraSinghParihar Over a year ago

should work. what language you are using. can you give us the code block you are using to execute the query?

Jon K. Over a year ago

Sorry for the delay, first day back at work... Using Delphi with this code:

Query1.Close;     Query1.SQL.Clear;     Query1.SQL.Add('SELECT * FROM sales_ledger WHERE REPLACE(telephone, " ", "") LIKE "%' + SearchEdit.Text + '%"');     Query1.Open;

Sashi Kant · Accepted Answer · 2016-10-01 11:06:52Z

4

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

edited Oct 1, 2016 at 11:06

answered Dec 28, 2012 at 11:57

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

3 Comments

Jon K. Over a year ago

The first statement returns another 'Column not found' error and the second gets me an 'Invalid table specification' error. I've added information to the original post and apologies for not mentioning it first time. Thanks for your time!

Jon K. Over a year ago

Now getting 'Table does not exist. General SQL error. Table not found.' Thanks again.

Sashi Kant Over a year ago

The first query or the second one?

Collectives™ on Stack Overflow

Using REPLACE() in MySQL SELECT

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related