Query For Special Characters in String

[razorking]

Does anyone know if it is possible to query a field for special characters in a string? I want to find any records that have characters other than A-Z, 0-9

Thanks!

 

[pr2-eugin]

Does that also include a-z?

It sure is possible, you can create a function to test this..

 

[razorking]

The query would need to show - any records in the field that had any characters that were not A-Z or 0-9

For example:
Field1
ABC123 - don't show
ABC/123 - I need to see this record
ABC.123 - I need to see this record
ABC$123 - I need to see this

 

[pr2-eugin]

So what happens for abc123, aBc123?

 

[pr2-eugin]

Create a Query something like..

SELECT someFields FROM theTableName
WHERE [B]IsSpecial[/B](theFieldYouAreTesting) = True;

Where IsSpecial is a user defined function like..

Public Function IsSpecial(tmpStr As String) As Boolean
[COLOR=Green]'***********************************
'Code Courtesy of
'  Paul Eugin
'**********************************[/COLOR]
    Dim lLng As Long, iCtr As Long
    lLng = Len(tmpStr)
    If lLng = 0 Then
        IsSpecial = True
        Exit Function
    End If
    For iCtr = 1 To lLng
        Select Case Asc(Mid(tmpStr, iCtr, 1))
            Case 48 To 57, 65 To 90, 97 To 122
                [COLOR=Green]'do nothing[/COLOR]
            Case Else
                IsSpecial = True
                Exit Function
        End Select
    Next
    IsSpecial = False
End Function

Copy the code into a common module, save and compile, so that Access understands the function, then use it in the SQL query..

 

[stopher]

you could play around with the LIKE statement in SQL. The following example selects all records that contain a character that is not alpha=numeric

Like "*[!A-Z0-9]*"

or if you have a set format:

Like "[A-Z][A-Z][A-Z][!A-Z0-9][0-9][0-9][0-9]"

hth
Chris

 

[razorking]

So what happens for abc123, aBc123?

These would not show because they contain no special characters. I only want to see records that contain any characters in the string that are not A-Z - 0-9

#@!%^&*()/\][ and the like

 

[razorking]

you could play around with the LIKE statement in SQL. The following example selects all records that contain a character that is not alpha=numeric

Like "*[!A-Z0-9]*"

or if you have a set format:

Like "[A-Z][A-Z][A-Z][!A-Z0-9][0-9][0-9][0-9]"

hth
Chris

I must be missing something: If I use: Like "*[!A-Z0-9]*" it shows everything

 

[pr2-eugin]

Okay try the function I have given you in Post#5..

 

[stopher]

I must be missing something: If I use: Like "*[!A-Z0-9]*" it shows everything

Please post an example of a record that is showing that should not show