CODEWITHSUNDEEP
ms-accessvbams-access-2013
Mark Tandy
Mark Tandy

Reputation: 13

MS Access Query: Replace all Characters in a String with a Single Character

I normally figure out (or successfully google!) a solution to most things I don't know, but this one's got me stumped and I'm sure someone must've done it in the past.

On paper what I'm trying to is quite simple (I thought!)

Basically I'm trying to reproduce the text string in Field1 in Field2, but with all the alphabet characters replaced with a single character, i.e. "x".

For example:

Field1 | Field2

Mark Tandy | xxxx xxxxx

Basically, I want to obscure the Field1 data. Better still, I'd like to be able to leave the first letter of each word unobscured, i.e.

Field1 | Field2

Mark Tandy | Mxxx Txxxx

There's probably a perfect simple solution (probably involving a custom function) that I'm not thinking laterally enough to figure out.

I'll keep working at it but if anyone has any suggestions I'd be really grateful!

Many thanks,

Mark

Upvotes: 1

Views: 473

Answers (1)

Erik A
Erik A

Reputation: 32642

You can solve this quite easily with a custom function:

Public Function ObscureString(InputString As String) As String
    Dim SplitString() As String
    SplitString = Split(InputString, " ") 'Split words on spaces
    Dim SingleString As Variant
    For Each SingleString In SplitString
        If ObscureString <> "" Then ObscureString = ObscureString & " " 'Append space
        ObscureString = ObscureString & Left(SingleString, 1) 'Append first character
        If Len(SingleString) > 1 Then
            ObscureString = ObscureString & String(Len(SingleString) - 1, "x") 'x-es
        End If
    Next
End Function

Upvotes: 1

Related Questions