Replace characters

[bennylynch]

Hi, i am looking to replace characters in a field using a query, I can use the replace to replace them all but that aint what I want e.g.

I want this: ~~test~test~~~~~test~~

To become this: test~test~test

I can use replace and get this testtesttest, but I need the ~ placed back between each word.

Any help appreciated, thanks

 

[raskew]

Hi -

Try this function (written in A97 - no embedded Replace() function available).

Function OneSpace2(pstr As String, delim As String) As String
'*******************************************
'Purpose:   Removes prefix, suffix and
'           excess characters from a string
'coded by   raskew
're:        http://www.access-programmers.co.uk/forums/newreply.php?do=postreply&t=148561
'To call:   ? onespace2("~~~test~~test~test~~", "~")
'Output:    "test~test~test"
'*******************************************

Dim strHold As String

    strHold = Trim(pstr)

    'handle prefixes
    Do While Left(strHold, 1) = delim
       strHold = trim(Mid(strHold, 2))
    Loop

   'handle suffixes
    Do While Right(strHold, 1) = delim
       strHold = trim(Left(strHold, Len(strHold) - 1))
    Loop
  
   'process body  
    Do While InStr(strHold, delim + delim) > 0
      strHold = trim(Left(strHold, InStr(strHold, delim + delim) - 1) & Mid(strHold, InStr(strHold, delim + delim) + 1))
    Loop
    OneSpace2 = trim(strHold)
    
End  Function

HTH - Bob

 

[DALeffler]

You can nest the Replace functions:

Expr1: Replace(Trim(Replace(Replace(Replace(Replace([MyFieldName],"~~","~"),"~~","~"),"~~","~"),"~"," "))," ","~")

While that works for the string example given, it messes up any other records that contain " " (spaces).

I'd call a custom function:

Function fncReplaceDoubles(strPassedString As String) As String

Do While InStr(1, strPassedString, "~~")
    strPassedString = Replace(strPassedString, "~~", "~")
Loop

If Left(strPassedString, 1) = "~" Then strPassedString = Mid(strPassedString, 2)
If Right(strPassedString, 1) = "~" Then strPassedString = Left(strPassedString, Len(strPassedString) - 1)

fncReplaceDoubles = strPassedString

End Function

hth,

 

[bennylynch]

Thanks for all your replies,great forum.

FAO: DALeffler, I think your nested Replace might be what I am looking for, I'll let you know, cheers everyone

 

[raskew]

Doug -

What happens if the string begins with "~~~" or ends with " ~~~~"?

Bob

 

[georgedwilkinson]

Bobs function removes those. That's what:
'To call: ? onespace2("~~~test~~test~test~~", "~")
'Output: "test~test~test"

means.

 

[DALeffler]

Doug -

What happens if the string begins with "~~~" or ends with " ~~~~"?

Bob

?Replace(Trim(Replace(Replace(Replace(Replace("~~~test~test~~test~~~test~~~~","~~","~"),"~~","~"),"~~","~"),"~"," "))," ","~")
test~test~test~test
?fncreplacedoubles("~~~test~test~~test~~~test~~~~")
test~test~test~test

Notice the two left most nested Replace functions do not replace "~~" with "~". The Replace to the immediate right of the Trim call replaces "~" with spaces, then the string is trimmed, then the last (left most) Replace function replaces " " with "~". Not elegant but it works.

The limitation of the above nesting is with the number of contiguous repeating characters "inside" the string.

The custom function simply replaces all "~~" with "~", then checks for "~" at the beginning and end of the string.

 

[bennylynch]

Thanks for all the replies, the nested Replace worked with a small tweak,

Thank you.