This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Visual Studio 6.0

January 2001

Separate But Equal Wildcard Characters in SQL Server and Access

by Mike D. Jones

Applications: Visual Basic 5.0-6.0

Download

In the article "Alleviate user frustration with the Like keyword and fuzzy searches," we showed you how to create a parsing routine that initiated fuzzy searches through data records. While we only used the asterisk (*) character, Visual Basic provides several other ways to pattern-match in a search string. Typically, you'll use pattern-matching characters for two purposes: to search for strings in a VB application's GUI or variable; or to look for items within a database, such as Access or SQL. When you search for pattern matches in a database however, depending on which database you're using, different characters perform different matches. In this article, we'll explain how the wildcard characters work, and which ones to use for Access or SQL Server.

A complete list of wildcards

Table A shows a complete list of wildcard characters. These standard VB characters will also work in Access SQL statements. However, notice that for SQL Server statements, you'll need to use different characters entirely.

Table A: Pattern-matching characters

VB/Access SQL Server Matches
? _ Any single character
* % Zero or more characters
# n/a Any single digit (0-9)
[charlist] [charlist] Any single character in charlist
[!charlist] [^charlist] Any single character not in charlist

The asterisk, question mark and pound sign characters

If you already read the article on fuzzy searches, then you probably know how the asterisk character works. In essence, this wildcard finds a match for any character or characters in the search string. For instance, the search string Fre*dom would find matches in freedom, freakdom, fredom and freeeedom. When pulling data from a SQL Server database, use the percent sign (%) instead. The question mark (?) character, on the other hand, determines a match for single characters only. Unlike the asterisk character, it won't search for multiple characters. So, the search string Bag?ins would find matches with baggins, bagzins but not bagggins or begins (and especially not whathashegotinhispockets, but you probably knew that). SQL Server uses the underscore (_) in place of the question mark.

The pound sign (#) character works in similar fashion to the question mark, only with digits instead of characters. With this in mind, the string 14#55 would match 14555, 14955, but not 143455 or 1455. SQL Server doesn't offer this option. To provide the same type of search in that database, you'll need to create your own character set, which we cover next.

Defining your own character sets

Character sets let you define several options for a single character in the target string. For example, to search for the letters a, b or c in the second character of any word, you'd use

?[abc]*

In addition, you can also define a range within a character set, as in

?[af-m]*

which would match any word with the letters a and f through m as its second character. When placed within brackets, the exclamation mark (!) indicates that the listed characters should not be considered part of a match. So, as you can imagine,

#[!123]*

would indicate a match for only those numbers without 1, 2 or 3 as their second character. For SQL Server, to create the equivalent to the pound sign simply use

[0-9]

Pattern-matching in a WHERE clause

Pattern matches in SQL statements give you added flexibility for filtering the records you want to retrieve. To add a pattern-match to a SQL statement, you use the WHERE clause in conjunction with LIKE. A typical statement might look as follows:

SELECT myField, myField2
FROM myBigTable
WHERE (myField2 LIKE 'El*me?tK')

For SQL Server, the statement would look like this:

SELECT myField, myField2
FROM myBigTable
WHERE (myField2 LIKE 'El%me_tK')

Figure A shows a more complex pattern search as executed in Visual Basic's SQL Builder window.

Warning: When using the SQL Builder to create SQL statements, keep in mind that it only accepts SQL Server pattern-matching characters--even when you're pulling data from an Access database.

Figure A: We used VB's SQL Builder tool to create complex, pattern-matching criteria.
[ Figure A ]

Searching for the wildcard itself

Often, however, you may want to actually search for strings that contain one of the wildcard characters themselves, such as 50%. The simplest way to do so is to wrap the wildcard character in brackets. This technique works in Visual Basic, Access and SQL Server. So, for instance, to find any strings that contain a percent character (assuming, of course, that the percent character is actually a wildcard character) you could use a SQL statement like the following:

SELECT txtCounty
FROM tblCounties
WHERE (txtCounty LIKE '%[%]%')

Don't let SQL Server and Access differences drive you wild

No doubt through the course of your Visual Basic programming career, you'll have cause to use both Access and SQL Server as a back-end database. Each database, however, uses slightly different wildcard characters to perform pattern-matching commands. In this article, we've explained those differences and shown you how to use pattern-matching characters in both databases.

Copyright © 2001 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.

Show: