Microsoft Access 掲示板

■正規表現版

Public Function SampleA(ByVal argStr As String _
                      , ByVal argSrc As String _
                      , ByVal argDst As String) As String
    If Len(argStr) < 2 Then
        SampleA = argStr
        Exit Function
    End If
    
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = "([a-z])(" & argSrc & ")([a-z])"
        If .Test(argStr) Then
            SampleA = .Replace(argStr, "$1" & argDst & "$3")
        Else
            SampleA = argStr
        End If
    End With
End Function

　
■InStr関数+ループ版

Public Function SampleB(ByVal argStr As String _
                      , ByVal argSrc As String _
                      , ByVal argDst As String) As String
    If Len(argStr) < 2 Then
        SampleB = argStr
        Exit Function
    End If
    
    Dim pos    As Long
    Dim front  As String
    Dim back   As String
    Dim target As String
    
    pos = 1
    target = argStr
    
    While pos > 0
        pos = InStr(pos + 1, target, argSrc, vbBinaryCompare)
        If pos > 0 And pos < Len(argStr) Then
            back = Mid$(target, pos - 1, 1)
            front = Mid$(target, pos + 1, 1)
            If StrConv(front & back, vbLowerCase) Like "[a-z][a-z]" Then
                Mid(target, pos, 1) = argDst
            End If
        End If
    Wend
    
    SampleB = target
End Function

　
■SQL

SELECT
    SampleA(
        '株式会社zawaーZawa　worldーrescue株式会社トーキョー'
      , 'ー'
      , '▼'
    ) as a
    , SampleB(
        '株式会社zawaーZawa　worldーrescue株式会社トーキョー'
      , 'ー'
      , '▲'
    ) as b
;

■結果