REPLACE in SQL

[albertkhor]

Why below code cannot work?

SELECT MobileTel1 FROM Contact WHERE (REPLACE(REPLACE(REPLACE(MobileTel1, '+', ''), '-', ''), ' ', '') LIKE '%" & phone & "%')"

 

[ManicCW]

you cannot structure a column name like that. for that you will have to use exec command.

exec ("SELECT ...")

 

[albertkhor]

you cannot structure a column name like that. for that you will have to use exec command.

exec ("SELECT ...")

thanks for your reply. Of cos i know i need to use exec command in order to run the sql statement~

i hope some one can help me to find out why below SQL statement cannot exec:
"SELECT MobileTel1 FROM Contact WHERE (REPLACE(REPLACE(REPLACE(MobileTel1, '+', ''), '-', ''), ' ', '') LIKE '%" & phone & "%')"

Below is another SELECT statement which i use now (can work):
"SELECT Mobile_Tel1 FROM Contact WHERE MobileTel1 LIKE '%" & phone & "%'"

 

[ManicCW]

I dont think you understand you cannot use variable in where clause. you can use that in stopred procedure with exec command.

 

[TechGnome]

Let's start from the begining....

HOW are you calling the SQL.... are you building it in VB, or is this a part of a stored procedure?

-tg

 

[albertkhor]

Let's start from the begining....

HOW are you calling the SQL.... are you building it in VB, or is this a part of a stored procedure?

-tg

By using Data Adapter as below:
New OleDbDataAdapter(SQL, oleDbConn)

 

[ManicCW]

if you are using it in code than i dont think this is possible. you will have to use it as paert of the stored procedure.

 

[albertkhor]

if you are using it in code than i dont think this is possible. you will have to use it as paert of the stored procedure.

what mean by stored procedure? can you explain more clear to me? thanks a lot~

 

[ManicCW]

stored procedure is database object in ms sql server. There you can write T-SQL code to manipulate data. In stored procedure you can do what you need in your example. Check help about stored procedures first!

 

[TechGnome]

Manic - no offence, but I think you're confusing things... .it looks like what he's done is to create a concatenated SQL string in VB, then is using the DataAdaptor to call (execute) it.... all of which is perfectly valid....

I'm assuming the code looks something like this:

Dim SQL As String
SQL = "SELECT MobileTel1 FROM Contact WHERE (REPLACE(REPLACE(REPLACE(MobileTel1, '+', ''), '-', ''), ' ', '') LIKE '%" & phone & "%')"

Dim DA As OLEDBDataAdaptor = New OleDbDataAdapter(SQL, oleDbConn)

right?

Now.... are you using Access then (since you are using OLEDB I'm assuming you are NOT using SQL Server)? What error do you get? At what point do you get the error? I'm assuming it's when you try to .Fill your datatable (or dataset?)

-tg

 

[ManicCW]

you are right i did not see oledb
i was thinking of sql server all the time because he did not write oledb in his first post.

 

[albertkhor]

Manic - no offence, but I think you're confusing things... .it looks like what he's done is to create a concatenated SQL string in VB, then is using the DataAdaptor to call (execute) it.... all of which is perfectly valid....

I'm assuming the code looks something like this:

Dim SQL As String
SQL = "SELECT MobileTel1 FROM Contact WHERE (REPLACE(REPLACE(REPLACE(MobileTel1, '+', ''), '-', ''), ' ', '') LIKE '%" & phone & "%')"
 
Dim DA As OLEDBDataAdaptor = New OleDbDataAdapter(SQL, oleDbConn)

right?

Now.... are you using Access then (since you are using OLEDB I'm assuming you are NOT using SQL Server)? What error do you get? At what point do you get the error? I'm assuming it's when you try to .Fill your datatable (or dataset?)

-tg

ya i'm using Access (.mdb) as my database. Below is the error message i get. i'm using dataset, but i do not think is my dataset problem cos if i try "SELECT * FROM Contact" it can work perfectly.

 

[ManicCW]

try running that sql statement in access query directly in database. see if it works there, if not post error here.

 

[TechGnome]

The answer is in that pic of the error message.... Replace doesn't exists as a valid function name... So.... the problem would seem straight forward to me. Althought I do think it odd that Access would have a problem with it.

-tg