Wildcards and non-Alphanumeric characters

grimpixie · February 23, 2005, 12:26pm

Is there a wildcard that seeks out non-alphanumeric characters in the same way that “?” is specific to letters and “#” is specific to numbers?

I’m wanting to find any company names in our database which use non-alphanumeric characters - I’m using Access to do the searching, if that makes a difference…

Thanks in advance
Grim

dlack · February 23, 2005, 4:18pm

In the Microsoft Access Help, search for “wildcard character.”

My PC has Access 2003, and the following is what I get in the help file. I think you would want to do [!?#].

ANSI-89 wildcard characters
Use this set of wildcard characters when you use the Find and Replace dialog box to find and optionally replace data in an Access database or an Access project. You also use these characters when you run select and update queries against an Access database, but you do not use them in queries run against an Access project.

Character Description Example

Matches any number of characters. You can use the asterisk anywhere in a character string. wh* finds what, white, and why, but not awhile or watch.
? Matches any single alphabetic character. B?ll finds ball, bell, and bill
Matches any single character within the brackets. B[ae]ll finds ball and bell but not bill
! Matches any character not in the brackets. b[!ae]ll finds bill and bull but not ball or bell

Matches any one of a range of characters. You must specify the range in ascending order (A to Z, not Z to A). b[a-c]d finds bad, bbd, and bcd

Matches any single numeric character. 1#3 finds 103, 113, 123

ANSI-92 wildcard characters
Use this set of wildcard characters when you run queries against Access projects (.adp files), and when using queries or the Find and Replace dialog box to search databases set to use the ANSI-92 standard.

Character Description Example
% Matches any number of characters. It can be used as the first or last character in the character string. wh% finds what, white, and why, but not awhile or watch.
_ Matches any single alphabetic character. B_ll finds ball, bell, and bill
Matches any single character within the brackets. B[ae]ll finds ball and bell but not bill
^ Matches any character not in the brackets. b[^ae]ll finds bill and bull but not ball or bell

Matches any one of a range of characters. You must specify the range in ascending order (A to Z, not Z to A). b[a-c]d finds bad, bbd, and bcd

Notes

To find wildcard characters that reside in your data, enclose the character that you want to find in brackets, like so: [#]. Follow that rule when searching for asterisks (), question marks (?), pound signs (#), opening brackets ([), and hyphens (-). Do not use brackets when searching for exclamation points (!) or closing brackets (]). To find those characters using the Find and Replace dialog box, simply enter the character in the Find What box with no surrounding brackets. You follow the same approach when finding the characters with a query. For example, this syntax returns all records that contain an exclamation point: Like "!*".
For information about using the Find and Replace dialog box and queries, see Find wildcard characters in an Access database.

If you’re searching for a hyphen and other characters simultaneously, place the hyphen before or after all the other characters inside the brackets, like so: [-#] or [#-]. However, if you have an exclamation point (!) after the opening bracket, place the hyphen after the exclamation point: [!-].

To search for a pair of opening and closing brackets (), you must enclose both characters in brackets, like so: []. You must do this because Access interprets a single pair of brackets as a zero-length string (zero-length string: A string that contains no characters. You can use a zero-length string to indicate that you know no value exists for a field. You enter a zero-length string by typing two double quotation marks with no space between them (" ").).

grimpixie · February 23, 2005, 6:29pm

Nice idea - except that it doesn’t work. I get all the records returned by that search. I think the problem is that is this also returns spaces, and most company names have at least one of those…

dlack · February 23, 2005, 6:43pm

I’m not really familiar with Access searches, but did you try putting a space in the brackets? As in [!?# ]

grimpixie · February 23, 2005, 6:47pm

Yeah - I thought of that, but it didn’t work either… not sure why not though…

Grim

rjk · February 23, 2005, 6:50pm

I don’t have MSAccess here, and was never an expert anyway, but my first thought would be something like [^a-zA-Z0-9 ], i.e. find characters that aren’t alphanumeric. I don’t know if multiple ranges in the same condition will work. (Note that the space is in there now, as dlack pointed out. Preview is your friend!)

grimpixie · February 23, 2005, 6:56pm

You sir, are da MAN!! [sub]replace male with female where appropriate[/sub]

That’s cracked it - thanks everyone…

Grim

dlack · February 23, 2005, 6:59pm

Ah, so it’s the ANSI-92 instead of the ANSI-89 … whatever that means. : )

rjk · February 23, 2005, 7:59pm

Yes, “da MAN” is right.

Actually, I’m a little surprised that MSAccess is so close to old-style Unix regular expressions. I did stuff like this long, long ago in a land far away, and I’m also a little surprised that I remember any of it. But for what I was doing, character ranges showed up a lot, and it was also often handy to turn conditions around.

grimpixie · February 24, 2005, 7:53am

No, I needed to replace the ^ with a ! to get it to work, but the rest of it was spot on!!

Grim