Beginner Question about Functions...

Toad Data Point
1

Hi,

I’m brand-new to TOAD for Data Analysts.

I can’t get a very basic REPLACE function to work. I keep getting this error: “[Microsoft][ODBC Microsoft Access Driver] Undefined function ‘replace’ in expression.”

Entering the exact same query directly in Access works fine.

I’m using TOAD for Data Analysts v2.5.0.913 on Windows. I’m connected to an Access database.

TOAD Query:

select
custnum as “Customer #”,
custaddress as “Original Address”,
replace(custaddress, 'S. ', 'South ') as “Updated Address”
from
customers
;

I’ve hunted all over the place online to try to figure this out. What am I missing…???

Thanks.

2

Anyone…anyone…?

This is such a simple query, I can’t convince myself to continue using TOAD for more complex “real” tasks 'til I get it figured out!

Thanks in advance to anyone who can enlighten me.

3

Access macros and/or functions are not available outside of Access. Also a quick search shows that the REPLACE scalar function was not implemented for the Access ODBC driver.

http://msdn.microsoft.com/en-us/library/bb208907.aspx
http://msdn.microsoft.com/en-us/library/ms710249(VS.85).aspx

As a workaround you could create an access query that uses these functions and call that query from TDA.

4

You need to use a CASE statement instead:

select
custnum as “Customer #”,
custaddress as “Original Address”,
case when custaddress = ‘S.’ then ‘South’ end as “Updated Address”
from
customers