Replace Function Exception

[Bluejay07]

Hello,

This issue may have been addressed before, however I did not find a solution.

I want to use the replace function, although in my case it is working a little too well. In a string, I have several similar occurences to replace, although I want to replace using an exact match, not anything similar to it. Here's and example:

Dim strTest as string

strTest = "0593, 05930-A, 05930-B"
strTest = Replace$(strTest, "0593", "")

'Expected result:
'strTest = ", 05930-A, 05930-B"

'Actual result:
'strTest = ", 0-A, 0-B"

How can I change the string for an exact match and not affect the remaining string? Please also note that the matching string could be in any location in the string (either before a like match or even after a like match).

Any help would be appreciated.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[dilettante]

It already is replacing exact matches. What you want might be something more like a contextual match.

To do this you'll probably want to write an InStr() loop that finds matches and then checks the context before replacing your substring. I think this is actually easier using InStrRev(), because then you won't need to restart your search point after each substring replacement.

An alternative might be to use a RegExp object.

 

[Bluejay07]

Thanks for the response dilettante.
Maybe a poor choice of words. Yes the search is finding an exact match, but I'm talking about an exact match based on my parameters, not anyting that has something appended to it.

I was thinking about instr as well, however, wouldn't that still be essentially the same thing? Instr searches for a matching item and returns the position, using the 'exact match'. I'm not sure if it would work.

The other solution I thought of is using the split function and searching each array item, then put it back in a string. Although it seems like a lot of extra work.



If at first you don't succeed, then sky diving wasn't meant for you!

 

[gmmastros]

Whenever I run in to a situation like this, here is how I handle it.

First, I don't have spaces in my data in addition to the commas. So my string would look like this:

[tt]"0593,05930-A,05930-B"[/tt]

Then, I usually take advantage of the commas. Imagine if the string was this:

[tt]"[!],[/!]0593,05930-A,05930-B[!],[/!]"[/tt]

Then, you could replace ,0593, with empty string and all would be good. So...

strTest = "0593,05930-A,05930-B"
strTest = Replace$("," & strTest & ",", "," & "0593" & ",", "")

This can leave leading and trailing commas, but those are pretty easy to clean up.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bluejay07]

I like that approach George.
Thank you for suggesting that.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[strongm]

>an exact match based on my parameters, not anyting that has something appended to it.


You are, however, expecting the computer to guess your intentions. How can it know that you want to match the 0593 in "0593," rather than the 0503 in "05931"? It needs more iunformation to a) distinguish between them and b) know which one you meant

Regular Expressions are one way of being more explicit and flexible.


So, for example:

[blue]    Dim strTest As String
    
    strTest = "0593, 05930-A, 05930-B"

    With CreateObject("vbscript.regexp") 
        .Pattern = "\b0593\b"
        .Global = True
        strTest = .Replace(strTest, "")
    End With
    
    MsgBox strTest[/blue]

 

[CP60]

Or,

Public Function ReplaceX(sString As String, Remove As String) As String
[COLOR=blue]
    Dim ary() As String, i As Integer

    ary = Split(sString, ",")
    For i = LBound(ary) To UBound(ary)
        If ary(i) = Remove Then ary(i) = vbNullString
    Next i
    ReplaceX = Join(ary, ",")
[/color]
End Function

 

[Bluejay07]

Thank you everyone for your suggestions and code provided.
More options provide more learning opportunities.

If at first you don't succeed, then sky diving wasn't meant for you!