Function Contains Illegal Characters In String (1 Viewer)

Not open for further replies.


Nothing In Moderation
Oct 22, 2009
The InStr function can find a single character. In the case of a string in a table, illegal characters can cause problems with a query. Or perhaps they just should not be allowed for business rule reasons.
My SQL Server database uses Remote Servers connected to Oracle and other sources. In a import routine, my preference is to test (true/false) to determine if the strings meet nameing standards before importing them.

Keywords: This also uses an Array and a Loop

This is a short code summary of the function to find a list of illegal characters in a string.

Public Function ContainsIllegalCharactersInString(MyText As String) As Boolean
Dim Illegal As Variant, i As Integer, j As Integer
On Error GoTo err_TRAP
Illegal = Array("\", "/", ":", "*", "?", "#", """", _
 "<", ">", "|")
 For i = 0 To UBound(Illegal)
    j = InStr(1, MyText, Illegal(i))
    If j > 0 Then
        'MsgBox "illegal character! " & illegal(i) ' for debug or testing
        ContainsIllegalCharactersInString = True
        Exit Function
        Exit For
    End If
 ContainsIllegalCharactersInString = False
Exit Function
 Debug.Print "Function ContainsIllegalCharactersInString " & Err.Description
End Function
To test in a debug window enter the command with the ?:
? ContainsIllegalCharactersInString("My Good String")
? ContainsIllegalCharactersInString("My String Gone Bad?")

Uncomment the Message Box to see specific character
Not open for further replies.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom