REPLACE part of string in field

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

REPLACE part of string in field

Post by sal21 »

i'm on Access and ado and vb6

in need a SQL to replace part of string in field LOCALITA...

for example i have CANICATTÌ replace Ì with I' to the end of query CANICATTI'

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: REPLACE part of string in field

Post by HansV »

Code: Select all

SQL = "UPDATE MyTable SET LOCALITA = Replace(LOCALITA,""Ì"",""I'"")"
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: REPLACE part of string in field

Post by sal21 »

HansV wrote:
23 Dec 2021, 11:02

Code: Select all

SQL = "UPDATE MyTable SET LOCALITA = Replace(LOCALITA,""Ì"",""I'"")"
IN debug.print have UPDATE [01-ABRUZZO] SET LOCALITA = Replace(LOCALITA,"Ì","I'")

and have error, in image...
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: REPLACE part of string in field

Post by HansV »

I can't explain that, sorry.
Best wishes,
Hans

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: REPLACE part of string in field

Post by SpeakEasy »

What connection string are you using?

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: REPLACE part of string in field

Post by sal21 »

HansV wrote:
23 Dec 2021, 11:36
I can't explain that, sorry.
THIS sql work in vb6, but restrict by other parameter Len and Mid, i dont need tath...

Code: Select all

SQL = "UPDATE " & "[" & RS1!TABLE_NAME & "]" & _
                " SET LOCALITA = MID( " & _
                " LOCALITA, " & _
                " 1, " & _
                " INSTR(LOCALITA, '" & strOld & "') " & _
                " - 1 " & _
                " ) " & _
                " + '" & strNew & "' " & _
                " + MID( " & _
                " LOCALITA, " & _
                " INSTR(LOCALITA, '" & strOld & "') " & _
                " + LEN('" & strOld & "'), " & _
                " LEN(LOCALITA) " & _
                " - INSTR(LOCALITA, '" & strOld & "') " & _
                " - LEN('" & strOld & "') " & _
                " + 1 " & _
                " )" & _
                " WHERE LOCALITA LIKE '*" & strOld & "*'"

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: REPLACE part of string in field

Post by HansV »

I don't understand that, sorry. Can you explain more clearly?
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: REPLACE part of string in field

Post by sal21 »

Other possibile error Is olejet 4.0, dont accept replace parameter....
I go To test with a driver ole 12.

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: REPLACE part of string in field

Post by SpeakEasy »

I ask because some older providers do not support the replace function.

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: REPLACE part of string in field

Post by sal21 »

HansV wrote:
23 Dec 2021, 16:22
I don't understand that, sorry. Can you explain more clearly?
GOOGLING....

can you modify your first sql with.....

There is no 'REPLACE' function in Jet SQL. The query works in Access because
Access is using the VBA Replace function, but that function can not be used
in Jet queries when they are executed outside of the Microsoft Access
environment. You may be able to achieve the same result in a query using
string-chopping functions (Left, Right, Mid, etc) or you may need to do the
replace in code by opening a recordset and looping through the records.
--
B***** R******** (MVP)

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: REPLACE part of string in field

Post by HansV »

Code: Select all

    SQL = "UPDATE [" & RS!!TABLE_NAME & "] SET LOCALITA = LEFT(LOCALITA,INSTR(LOCALITA,""Ì"")-1) " & _
        "& ""I'"" & MID(LOCALITA,INSTR(LOCALITA,""Ì"")+4) WHERE LOCALITA LIKE ""*Ì*"""
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: REPLACE part of string in field

Post by sal21 »

HansV wrote:
24 Dec 2021, 15:43

Code: Select all

    SQL = "UPDATE [" & RS!!TABLE_NAME & "] SET LOCALITA = LEFT(LOCALITA,INSTR(LOCALITA,""Ì"")-1) " & _
        "& ""I'"" & MID(LOCALITA,INSTR(LOCALITA,""Ì"")+4) WHERE LOCALITA LIKE ""*Ì*"""
WOW!!!!

But my parameter are variable i need to insert in the SQL 2 variable similar:

strOld = "Ì"
strNew = "I"

sorry me.

note:
in this time not consider the "'" for I but simply I
and the lenght of strOld are variable, strNew is fixed for 1 digit

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: REPLACE part of string in field

Post by HansV »

Sigh... I thought you wanted to get rid of the parameters. What is wrong with the code that you posted earlier?
Best wishes,
Hans

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: REPLACE part of string in field

Post by SpeakEasy »

>[replace] can not be used in Jet queries
Not true. It depends on the provider. Old providers didn't support it. Newer ones, such as ACE OleDB 12, can. This is why I asked about your connection string earlier on.

It is also quite important to check the dates of info you find on the web, as things can change quite quickly in the IT world. The post you quote is 17 tears old - and 17 years ago was accurate.

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: REPLACE part of string in field

Post by sal21 »

HansV wrote:
24 Dec 2021, 15:43

Code: Select all

    SQL = "UPDATE [" & RS!!TABLE_NAME & "] SET LOCALITA = LEFT(LOCALITA,INSTR(LOCALITA,""Ì"")-1) " & _
        "& ""I'"" & MID(LOCALITA,INSTR(LOCALITA,""Ì"")+4) WHERE LOCALITA LIKE ""*Ì*"""
goodmorning ...

see wth i have with your sql, in debug.print:

UPDATE [01-ABRUZZO] SET LOCALITA = LEFT(LOCALITA,INSTR(LOCALITA,"Ì")-1) & "I'" & MID(LOCALITA,INSTR(LOCALITA,"Ì")+4) WHERE LOCALITA LIKE "*Ì*"

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: REPLACE part of string in field

Post by HansV »

Does it work?
Best wishes,
Hans

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: REPLACE part of string in field

Post by SpeakEasy »

Given up on Replace then?