REPLACE part of string in field
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
REPLACE part of string in field
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'
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'
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: REPLACE part of string in field
Code: Select all
SQL = "UPDATE MyTable SET LOCALITA = Replace(LOCALITA,""Ì"",""I'"")"
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
Re: REPLACE part of string in field
IN debug.print have UPDATE [01-ABRUZZO] SET LOCALITA = Replace(LOCALITA,"Ì","I'")HansV wrote: ↑23 Dec 2021, 11:02Code: Select all
SQL = "UPDATE MyTable 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.
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 4StarLounger
- Posts: 550
- Joined: 27 Jun 2021, 10:46
Re: REPLACE part of string in field
What connection string are you using?
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
Re: REPLACE part of string in field
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 & "*'"
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: REPLACE part of string in field
I don't understand that, sorry. Can you explain more clearly?
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
Re: REPLACE part of string in field
Other possibile error Is olejet 4.0, dont accept replace parameter....
I go To test with a driver ole 12.
I go To test with a driver ole 12.
-
- 4StarLounger
- Posts: 550
- Joined: 27 Jun 2021, 10:46
Re: REPLACE part of string in field
I ask because some older providers do not support the replace function.
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
Re: REPLACE part of string in field
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)
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: REPLACE part of string in field
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
Hans
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
Re: REPLACE part of string in field
WOW!!!!HansV wrote: ↑24 Dec 2021, 15:43Code: Select all
SQL = "UPDATE [" & RS!!TABLE_NAME & "] SET LOCALITA = LEFT(LOCALITA,INSTR(LOCALITA,""Ì"")-1) " & _ "& ""I'"" & MID(LOCALITA,INSTR(LOCALITA,""Ì"")+4) WHERE LOCALITA LIKE ""*Ì*"""
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
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: REPLACE part of string in field
Sigh... I thought you wanted to get rid of the parameters. What is wrong with the code that you posted earlier?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 550
- Joined: 27 Jun 2021, 10:46
Re: REPLACE part of string in field
>[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.
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.
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
Re: REPLACE part of string in field
goodmorning ...HansV wrote: ↑24 Dec 2021, 15:43Code: Select all
SQL = "UPDATE [" & RS!!TABLE_NAME & "] SET LOCALITA = LEFT(LOCALITA,INSTR(LOCALITA,""Ì"")-1) " & _ "& ""I'"" & MID(LOCALITA,INSTR(LOCALITA,""Ì"")+4) WHERE LOCALITA LIKE ""*Ì*"""
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 "*Ì*"
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 4StarLounger
- Posts: 550
- Joined: 27 Jun 2021, 10:46
Re: REPLACE part of string in field
Given up on Replace then?