1

I'm trying to write a query in Access. Is it possible to do a wildcard replace (something like this)?

SELECT REPLACE(MyStatus, '<div' & % & '>', '') FROM tblWork

I have data similar to this: <div class="Style123">Status: 1/01/2019 Work was completed.

I want to transform the query results to this: Status: 1/01/2019 Work was completed.

Note: I DO NOT want to update the data in the table.

crjunk
  • 949
  • 4
  • 21
  • 40
  • No. And if you need to do this, I might suggest switching to Postgres (although I'm not supposed to recommend databases) or to MariaDB. – Gordon Linoff Mar 21 '19 at 20:19
  • I didn't think so. Trying to find a quick and easy solution to strip out all the HTML tags. – crjunk Mar 21 '19 at 20:24

3 Answers3

0

Not without VBA.

Since you can use VBA functions in queries, this certainly is possible. You can use the following function:

Public Function LikeReplace(strInput As String, strPattern As String, strReplace As String, Optional start As Long = 1)
    Dim LenCompare As Long
    Do While start <= Len(strInput)
        For LenCompare = Len(strInput) - start + 1 To 1 Step -1
            If Mid(strInput, start, LenCompare) Like strPattern Then
                strInput = Left(strInput, start - 1) & strReplace & Right(strInput, Len(strInput) - (start + LenCompare - 1))
            End If
        Next
        start = start + 1
    Loop
    LikeReplace = strInput
End Function

This uses the VBA LIKE pattern to match. You can then implement it in your query:

SELECT LikeReplace(MyStatus, '<div*>', '') FROM tblWork

Performance will suffer, though, both because the VBA function is not high performance and because calling VBA from queries causes overhead. And this can't be used from external applications.

For more advanced pattern matching, you can use a VBA UDF that uses regex, as shared here by Krish KM

Erik A
  • 31,639
  • 12
  • 42
  • 67
0

You can create a tiny helper function:

Public Function StripDiv(ByVal Input As String) As String

    Dim Result As String

    If UBound(Split(Input, ">")) > 0 Then
        Result = Split(Input, ">")(1)
    Else
        Result = Input
    End If

    StripDiv = Result

End Function

Then:

SELECT StripDiv([MyStatus]) FROM tblWork
Gustav
  • 53,498
  • 7
  • 29
  • 55
0

I think something like this could work for this situation. Even if its not using wildcards. 

select right( MyStatus 
, LEN(MyStatus ) - InStr(MyStatus , '>')
)
from tblWork;
CloudEmber
  • 99
  • 4