CODEWITHSUNDEEP
sqlms-access
Kieran Smith
Kieran Smith

Reputation: 119

Locate and create a list of all entries that contain uppercase

To give you an understanding of the situation, I've got a field that stores Email Addresses and I need to find any email addresses that contain uppercase characters, so ideally I need a query that will find any occurrences and display a full list but not make any changes. I have attempted to find something like this already but unfortunately not located anything similar.

The field name is 'sEmailAddress' and the table is called 'Buyer'

Thanks in advance.

Upvotes: 0

Views: 70

Answers (2)

Gustav
Gustav

Reputation: 55981

You can use StrComp:

Select *
From Buyer
Where StrComp(LCase(sEmailAddress), sEmailAddres, 0) <> 0

Upvotes: 3

Sergey S.
Sergey S.

Reputation: 6336

You can use VBA function like this in WHERE clause:

Public Function HasUppercase(str As Variant) As Boolean
    Dim i As Long
    If IsNull(str) Then
        Exit Function
    End If

    For i = 1 To Len(str)
        If Asc(Mid(str, i, 1)) <= 90 Then
            HasUppercase = True
        End If
    Next
End Function

Query:

SELECT *
FROM Table1
WHERE HasUppercase([field1])=True;

Upvotes: 0

Related Questions