Solved replace string with wildcard * (1 Viewer)

silentwolf

Active member
Local time
Today, 09:28
Joined
Jun 12, 2009
Messages
545
Hi again,

sorry guys cant seam to find something that works in my case.

I would love to replace a string with a "*" wildcard .

what I did try was this

Code:
Public Function ReplaceWuestenrot(ByVal strText As String) As String
    Dim strOld(0 To 1) As Variant
    Dim strNew As String
    Dim i As Integer

    strOld(0) = "W*stenrot"
    strNew = "Wüstenrot"

    For i = LBound(strOld) To UBound(strOld)
        strText = Replace(strText, strOld(i), strNew)
    Next i
    ReplaceWuestenrot = strText
End Function

but it does not work..

Would like to keep the rest of the string as it is just replace the word "Wuestenrot" or it could be "spellt wrong and be something like "Woastenrot"
so I like to find every possible way of it and change it to "Wüstenrot"

Could someone help please?

The above code only works when I write the correct spelling.


Many thanks
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:28
Joined
Feb 28, 2001
Messages
26,999
It is not enough to toss in a wildcard character. You also need to use the LIKE operator to find such a string.


However, in your case, it gets more complex because I don't recall that the REPLACE function accepts wildcard patterns and the online documentation seems to support that recollection.


You can look up the use of Regular Expressions as a way to match against any of several possible related character sequences and RegEx functions are supported in queries.


 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:28
Joined
Oct 29, 2018
Messages
21,358
Not sure if it applies; but also, maybe check out the SoundEx and Levenschtein functions.
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:28
Joined
Jan 23, 2006
Messages
15,364
Do you have some more examples of the misspellings?
Your sample shows the final "stenrot" is consistent, so you could check for the city name like "w*stenrot" and replace with Wüstenrot.

You may get some ideas from this test.
Code:
Sub sometests()
    Dim goodname As String
    goodname = "Wüstenrot"
    Dim i As Integer
    Dim a(5) As String
    a(0) = "w*t"
    a(1) = "w*nrot"
    a(2) = "w*en*t"
    a(3) = "w*e*r*t"
    a(4) = "wu*t"
    a(5) = "w*n*at"

    For i = LBound(a) To UBound(a)
        If goodname Like a(i) Then
            Debug.Print "a(" & i & ")  " & a(i) & " is a match "
        Else
            Debug.Print "a(" & i & ")  " & a(i) & " is NOT a match "
        End If
    Next i
End Sub

Results:
a(0) w*t is a match
a(1) w*nrot is a match
a(2) w*en*t is a match
a(3) w*e*r*t is a match
a(4) wu*t is NOT a match
a(5) w*n*at is NOT a match
 
Last edited:

silentwolf

Active member
Local time
Today, 09:28
Joined
Jun 12, 2009
Messages
545
Hi guys,

many thanks to you for your replies!
@TheDocMan thanks for your links I will look into them! RegEx I knowbut never really used them.. or at least seam complicated to me just yet.
But will look into it and hope to get a better understanding of them!
Thanks!
@theDBguy, Oh never heard of it before I must admit.. need to look into it and see what that is. Thanks for your suggestion.

@jdraw
Do you have some more examples of the misspellings?
well not really at present as there where misspelling as said like WAostenrot, and Wuestenrot, as sometimes they change the german "Ü" to "UE" .
Could of course at some occactions be completly different but that I can not forsee and is not necessary.
But at least something like what happend now and with the "Umlaut" as we call the "Ü" so that will be taken care of.

But I see your example and I am sure I will get something useful out of it!

Many thanks to you for this example!

Cheers again to all!

Have a great Sunday )
 

silentwolf

Active member
Local time
Today, 09:28
Joined
Jun 12, 2009
Messages
545
Hi guys,

I just have done it like this now.

Sure not the elegant way of doing it but it works :)

With RegEx I still need to look further into it as I have still troubles with getting the rest of the string displayed.

What I mean is that it is difficult for me at least that I get an Regular Expression to change a substring and display all other words before and afterwards.

String"Wuestenrot Versicherungs-AG something Else"

To use RegEx to change the string to"Wüstenrot Versicherung-AG something Else"

Need to take more time to find something that explains that better to me. There are many Examples of how to change a Word but not sure how to put it all together just yet.

But at least I did get it to manage with a simple UDF like that

Code:
Function UpdateWuestenrot(strText As String) As String
    If strText Like "W*stenrot*" Then
        Dim intpos As Integer
        intpos = InStr(strText, " ")
            Dim strOld As String
            strOld = Left(strText, intpos - 1)
            UpdateWuestenrot = Replace(strText, strOld, "Wüstenrot")
        Else
        UpdateWuestenrot = strText
    End If
End Function

But still appreciate all your links and your input I will take more time to get the grasp on it .)

Cheers
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:28
Joined
Oct 29, 2018
Messages
21,358
Hi guys,

I just have done it like this now.

Sure not the elegant way of doing it but it works :)

With RegEx I still need to look further into it as I have still troubles with getting the rest of the string displayed.

What I mean is that it is difficult for me at least that I get an Regular Expression to change a substring and display all other words before and afterwards.

String"Wuestenrot Versicherungs-AG something Else"

To use RegEx to change the string to"Wüstenrot Versicherung-AG something Else"

Need to take more time to find something that explains that better to me. There are many Examples of how to change a Word but not sure how to put it all together just yet.

But at least I did get it to manage with a simple UDF like that

Code:
Function UpdateWuestenrot(strText As String) As String
    If strText Like "W*stenrot*" Then
        Dim intpos As Integer
        intpos = InStr(strText, " ")
            Dim strOld As String
            strOld = Left(strText, intpos - 1)
            UpdateWuestenrot = Replace(strText, strOld, "Wüstenrot")
        Else
        UpdateWuestenrot = strText
    End If
End Function

But still appreciate all your links and your input I will take more time to get the grasp on it .)

Cheers
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:28
Joined
Jan 23, 2006
Messages
15,364
I'd be very cautious of this condition
Code:
If strText Like "W*stenrot*" Then
But it seems your
Code:
intpos = InStr(strText, " ")
will catch/limit longer text(s).

Good to see you have a working solution.
 

silentwolf

Active member
Local time
Today, 09:28
Joined
Jun 12, 2009
Messages
545
I have it combined with this UpdateQuery

Code:
Sub UpdateWuestenrotUmsatztext()
    Dim strSQL As String
    strSQL = "UPDATE qryLastschriften SET Umsatztext = UpdateWuestenRot([UMSATZTEXT])"
    CurrentDb.Execute strSQL, dbFailOnError
End Sub

further I did

Code:
If strText Like "W*stenrot *" Then

so it catches if a Word the space too just incase there would be something similar with a longer string
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:28
Joined
Jan 23, 2006
Messages
15,364
What exactly is qryLastschriften? An Update query is to update a table???
 

silentwolf

Active member
Local time
Today, 09:28
Joined
Jun 12, 2009
Messages
545
Yes it is an update Query for a table

Well qryLastschriften is a query where I limit the filter the table and then update it.
 

Users who are viewing this thread

Top Bottom