Menu
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.
402
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.
To replace, use the REPLACE() MySQL function. Since you need to update the table for this, use the UPDATE() function with the SET clause.
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.
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.
SELECT * FROM sales_ledger)
64
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+"%'"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
