Replace function using the Jet engine

T

TheBrenda

I am writing an SQL statement that will be used by the Jet Engine. I want to
take field1 and replace all '-' and ":" with '_'. How can I do this? Have
tried using the following but "replace" is not found. have also tried my own
REPLACE1 function, but it is still not found.

Select *, replace(field1, '-', '_') as field2 from table1
 
D

Douglas J. Steele

Are you attempting to run this from within Access, or using some other
application? If Access, what version?
 
J

John W. Vinson

I am writing an SQL statement that will be used by the Jet Engine. I want to
take field1 and replace all '-' and ":" with '_'. How can I do this? Have
tried using the following but "replace" is not found. have also tried my own
REPLACE1 function, but it is still not found.

Select *, replace(field1, '-', '_') as field2 from table1
Click to expand...

The correct Access/JET SQL syntax to permanently update Table1.Field1 as
described would be

UPDATE Table1 SET Field1 = Replace(Replace([Field1], ":", "_"), "-", "_")
WHERE Field1 LIKE "*[:-]*";

If you just want to *display* a calculated field with these replacements,
rather than actually change the stored data in the table, it would be

SELECT Replace(Replace([Field1], ":", "_"), "-", "_") AS Field2 FROM Table1;
 
T

TheBrenda

From outside Access. I am running it using the Jet Engine connection string.
When I try Replace I get "undefined function Replace". From reading the
forums, I realize that the Jet does not have a Replace function and you
cannot use a UDF. Wondering what is the next best thing.

SELECT Replace(Replace([Field1], ":", "_"), "-", "_") AS Field2 FROM Table1;


Someone from work suggested something like the following (they were trying
to Replace tildas)

SELECT *,
IIF(INSTR(FieldID, '~') > 0 AND INSTR(FieldID, '(') = 0,
MID(FieldID, 1, INSTR(FieldID, '~') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '~') + 1)
,
IIF(INSTR(FieldID, '~') > 0 AND INSTR(FieldID, '(') > 0,
MID(FieldID, 1, INSTR(FieldID, '~') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '~') + 1, INSTR(FieldID, '(') -
INSTR(FieldID, '~') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '(') + 1, INSTR(FieldID, ')') -
INSTR(FieldID, '(') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, ')') + 1)
,
IIF(INSTR(FieldID, '~') = 0 AND INSTR(FieldID, '(') > 0,
MID(FieldID, 1, INSTR(FieldID, '(') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '(') + 1, INSTR(FieldID, ')') -
INSTR(FieldID, '(') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, ')') + 1)
,
FieldID
))) AS FunctionName
FROM Table1
 
D

Douglas J. Steele

Yup, you can't use most VBA functions when running from outside of Access.

What are you doing with Field2? Can you perhaps simply retrieve with the :
and - and manipulate it outside of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


TheBrenda said:
From outside Access. I am running it using the Jet Engine connection
string.
When I try Replace I get "undefined function Replace". From reading the
forums, I realize that the Jet does not have a Replace function and you
cannot use a UDF. Wondering what is the next best thing.

SELECT Replace(Replace([Field1], ":", "_"), "-", "_") AS Field2 FROM
Table1;


Someone from work suggested something like the following (they were trying
to Replace tildas)

SELECT *,
IIF(INSTR(FieldID, '~') > 0 AND INSTR(FieldID, '(') = 0,
MID(FieldID, 1, INSTR(FieldID, '~') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '~') + 1)
,
IIF(INSTR(FieldID, '~') > 0 AND INSTR(FieldID, '(') > 0,
MID(FieldID, 1, INSTR(FieldID, '~') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '~') + 1, INSTR(FieldID, '(') -
INSTR(FieldID, '~') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '(') + 1, INSTR(FieldID, ')') -
INSTR(FieldID, '(') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, ')') + 1)
,
IIF(INSTR(FieldID, '~') = 0 AND INSTR(FieldID, '(') > 0,
MID(FieldID, 1, INSTR(FieldID, '(') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '(') + 1, INSTR(FieldID, ')') -
INSTR(FieldID, '(') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, ')') + 1)
,
FieldID
))) AS FunctionName
FROM Table1





Douglas J. Steele said:
Are you attempting to run this from within Access, or using some other
application? If Access, what version?
Click to expand...
Click to expand...
 
J

John Spencer

That should have the same problem as REPLACE. INSTR (as far as I know) is a
VBA function also. Same goes for MID.

I don't know of any method that you can do this via a query with native JET SQL.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
From outside Access. I am running it using the Jet Engine connection string.
When I try Replace I get "undefined function Replace". From reading the
forums, I realize that the Jet does not have a Replace function and you
cannot use a UDF. Wondering what is the next best thing.

SELECT Replace(Replace([Field1], ":", "_"), "-", "_") AS Field2 FROM Table1;


Someone from work suggested something like the following (they were trying
to Replace tildas)

SELECT *,
IIF(INSTR(FieldID, '~') > 0 AND INSTR(FieldID, '(') = 0,
MID(FieldID, 1, INSTR(FieldID, '~') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '~') + 1)
,
IIF(INSTR(FieldID, '~') > 0 AND INSTR(FieldID, '(') > 0,
MID(FieldID, 1, INSTR(FieldID, '~') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '~') + 1, INSTR(FieldID, '(') -
INSTR(FieldID, '~') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '(') + 1, INSTR(FieldID, ')') -
INSTR(FieldID, '(') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, ')') + 1)
,
IIF(INSTR(FieldID, '~') = 0 AND INSTR(FieldID, '(') > 0,
MID(FieldID, 1, INSTR(FieldID, '(') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '(') + 1, INSTR(FieldID, ')') -
INSTR(FieldID, '(') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, ')') + 1)
,
FieldID
))) AS FunctionName
FROM Table1





Douglas J. Steele said:
Are you attempting to run this from within Access, or using some other
application? If Access, what version?
Click to expand...
Click to expand...
 
T

TheBrenda

I know that the IIF, INSTR and MID worik. Just ran it.

This has a list of supported functions.

http://support.microsoft.com/default.aspx?scid=kb;en-us;294698

John Spencer said:
That should have the same problem as REPLACE. INSTR (as far as I know) is a
VBA function also. Same goes for MID.

I don't know of any method that you can do this via a query with native JET SQL.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
From outside Access. I am running it using the Jet Engine connection string.
When I try Replace I get "undefined function Replace". From reading the
forums, I realize that the Jet does not have a Replace function and you
cannot use a UDF. Wondering what is the next best thing.

SELECT Replace(Replace([Field1], ":", "_"), "-", "_") AS Field2 FROM Table1;


Someone from work suggested something like the following (they were trying
to Replace tildas)

SELECT *,
IIF(INSTR(FieldID, '~') > 0 AND INSTR(FieldID, '(') = 0,
MID(FieldID, 1, INSTR(FieldID, '~') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '~') + 1)
,
IIF(INSTR(FieldID, '~') > 0 AND INSTR(FieldID, '(') > 0,
MID(FieldID, 1, INSTR(FieldID, '~') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '~') + 1, INSTR(FieldID, '(') -
INSTR(FieldID, '~') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '(') + 1, INSTR(FieldID, ')') -
INSTR(FieldID, '(') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, ')') + 1)
,
IIF(INSTR(FieldID, '~') = 0 AND INSTR(FieldID, '(') > 0,
MID(FieldID, 1, INSTR(FieldID, '(') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '(') + 1, INSTR(FieldID, ')') -
INSTR(FieldID, '(') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, ')') + 1)
,
FieldID
))) AS FunctionName
FROM Table1





Douglas J. Steele said:
Are you attempting to run this from within Access, or using some other
application? If Access, what version?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am writing an SQL statement that will be used by the Jet Engine. I want
to
take field1 and replace all '-' and ":" with '_'. How can I do this? Have
tried using the following but "replace" is not found. have also tried my
own
REPLACE1 function, but it is still not found.

Select *, replace(field1, '-', '_') as field2 from table1
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Cool. I learned something new already today.

Thanks for posting back.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I know that the IIF, INSTR and MID worik. Just ran it.

This has a list of supported functions.

http://support.microsoft.com/default.aspx?scid=kb;en-us;294698

John Spencer said:
That should have the same problem as REPLACE. INSTR (as far as I know) is a
VBA function also. Same goes for MID.

I don't know of any method that you can do this via a query with native JET SQL.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
From outside Access. I am running it using the Jet Engine connection string.
When I try Replace I get "undefined function Replace". From reading the
forums, I realize that the Jet does not have a Replace function and you
cannot use a UDF. Wondering what is the next best thing.

SELECT Replace(Replace([Field1], ":", "_"), "-", "_") AS Field2 FROM Table1;


Someone from work suggested something like the following (they were trying
to Replace tildas)

SELECT *,
IIF(INSTR(FieldID, '~') > 0 AND INSTR(FieldID, '(') = 0,
MID(FieldID, 1, INSTR(FieldID, '~') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '~') + 1)
,
IIF(INSTR(FieldID, '~') > 0 AND INSTR(FieldID, '(') > 0,
MID(FieldID, 1, INSTR(FieldID, '~') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '~') + 1, INSTR(FieldID, '(') -
INSTR(FieldID, '~') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '(') + 1, INSTR(FieldID, ')') -
INSTR(FieldID, '(') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, ')') + 1)
,
IIF(INSTR(FieldID, '~') = 0 AND INSTR(FieldID, '(') > 0,
MID(FieldID, 1, INSTR(FieldID, '(') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, '(') + 1, INSTR(FieldID, ')') -
INSTR(FieldID, '(') - 1)
+ '_' + MID(FieldID, INSTR(FieldID, ')') + 1)
,
FieldID
))) AS FunctionName
FROM Table1





:

Are you attempting to run this from within Access, or using some other
application? If Access, what version?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am writing an SQL statement that will be used by the Jet Engine. I want
to
take field1 and replace all '-' and ":" with '_'. How can I do this? Have
tried using the following but "replace" is not found. have also tried my
own
REPLACE1 function, but it is still not found.

Select *, replace(field1, '-', '_') as field2 from table1
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Replace text with related number 1
JET ENGINE ? 2
Exclude query advise 4
fields in a select query 1
Problem with SQL Server2000 SQL in Access Pass Through Query 2
GROUP BY??? 1
HOW TO GET TABLE DATA IN VARIABLE 3
Access 2007 - SQL query assistance needed 2

Top