Replace function using the Jet engine

  • Thread starter Thread starter TheBrenda
  • Start date Start date
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
 
Are you attempting to run this from within Access, or using some other
application? If Access, what version?
 
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;
 
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
 
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...
 
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...
 
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...
 
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...
 
Back
Top