Anyone who deals with SQL and Access queries is probably familiar with the LIKE statement and the use of the standard wildcards (% or *).

Less documented however is the additional wild cards that you should be aware of if you are building queries that will be run against your database when constructing more complicated LIKE statements.

The following describes the wildcard’s that are available in Microsoft SQL.

Microsoft T-SQL LIKE statement wildcards
Wildcard character	Description	Example
%	Any string of zero or more characters.	SELECT * FROM [myTable] WHERE [title] LIKE ‘%computer%’This will find all the entries the column ‘title’ from ‘myTable’ contains the word ‘computer’ anywhere in the field.
_ (underscore)	Any single character.	SELECT * FROM [myTable] WHERE [surname] LIKE ‘sm_th’ This will find all five-letter last names that start with ‘sm’ and end with ‘th’ (for example Smith, Smeth, etc).
[…]	Any single character within the specified range ([a-f]) or set ([abcdef]) or a numeric range [0-9].	SELECT * FROM [myTable] WHERE [surname] LIKE ‘[C-P]arsen’ This will find all the last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, Paresen, etc.
[^]	Any single character not within the specified range ([^a-f]) or set ([^abcdef]).	SELECT * FROM [myTable] WHERE [surname] LIKE ‘de[^l]%’ This will get all the last names starting with de and where the following letter is not l, for example it will return Desmond, but not Delimetry.

In addition after an MS SQL LIKE statement you can include an additional definition of an escape character, which can be placed in front of a wildcard character so that it is treated as a regular character or you could the square brackets as escape characters for your query, for example: –

SELECT * FROM [discounts] WHERE [discount] LIKE ’30[%]’ 
                    or
SELECT * FROM [discounts] WHERE [discount]
    LIKE ’30!%’ ESCAPE ‘!’

This will select any discounts that are 30%.

Reference: – http://msdn2.microsoft.com/en-us/library/ms179859.aspx

---

In Microsoft Access the wildcards and syntax is slightly different but similar

The following describes the wildcard’s that are available in Microsoft Access.

Microsoft Access Query LIKE statement wildcards
Wildcard character	Description	Example
*	Any string of zero or more characters.	SELECT * FROM [myTable] WHERE [title] LIKE ‘*computer*’ This will find all the entries the column ‘title’ from ‘myTable’ contains the word ‘computer’ anywhere in the field.
?	Any single character.	SELECT * FROM [myTable] WHERE [surname] LIKE ‘sm?th’ This will find all five-letter last names that start with ‘sm’ and end with ‘th’ (for example Smith, Smeth, etc).
[…]	Any single character within the specified range ([a-f]) or set ([abcdef]).	SELECT * FROM [myTable] WHERE [surname] LIKE ‘[C-P]arsen’  This will find all the last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, Paresen, etc.
[!…]	Any single character not within the specified range ([!a-f]) or set ([!abcdef]).	SELECT * FROM [myTable] WHERE [surname] LIKE ‘de[!l]*’ This will get all the last names starting with de and where the following letter is not l, for example it will return Desmond, but not Delimetry.
#	Any single numeric character.	SELECT * FROM [myTable] WHERE [postcode] LIKE ‘eh## #??’ This will get all the postcodes starting with EH followed by two numeric characters a space another numeric character and a further two characters, for example it would return EH54 6LY, EH55 8TZ, etc but not EH4 1NJ.

In Microsoft Access there is no ESCAPE definition so you must use square brackets, for example: –

SELECT

* FROM [discounts] WHERE [discount] LIKE ’30[%]’

This will select any discounts that are 30%.

---

Query Syntax in Microsoft Access on Linked SQL tables 

Now as anyone who has worked with Microsoft Access linked SQL tables will likely know, performing like queries can be complicated… this is because it can sometimes be difficult to determine where a SQL statement/query might be executed (on the SQL Server or my the local Microsoft Access engine).

As a general rule of thumb, if you are running the statement (Microsoft Access query or Access DAO Recordset) directly against the linked table, it will use the SQL Server syntax. For example: –

SELECT

* FROM tblContacts
    WHERE Firstname LIKE ‘Pa%’ –[WILL WORK]

If however your WHERE clause is in a join query or your query runs some kind of function on some of the data. For example : –

[a bad example at that, yes I would never recommend a statement like this]
SELECT Nz(contactid, –1), * FROM tblContacts
    WHERE Firstname LIKE ‘Pa%’ –[WILL NOT WORK]

This statement would not return the correct results and would have to be modified to the following: –

SELECT

Nz(contactid, –1), * FROM tblContacts
    WHERE Firstname LIKE ‘Pa*’ –[WILL WORK]

A more likely scenario would be where you are joining two tables within your Microsoft Access query and you needed to perform a like statement against it. For example: –

SELECT

* FROM tblContacts
    LEFT JOIN tblCompanies ON
    tblContacts.CompanyID = tblCompanies.CompanyID
    WHERE Firstname LIKE ‘Pa%’ –[WILL NOT WORK]

In MS Access this statement would not return the correct results, as the join is performed by MS Access and would have to be modified to the following: –

SELECT

* FROM tblContacts
    LEFT JOIN tblCompanies ON
        tblContacts.CompanyID = tblCompanies.CompanyID
    WHERE Firstname LIKE ‘Pa*’ –[WILL WORK]

This also applies to any MS Access Queries based other queries, even if the other Query is a direct Query against a linked SQL table.

---

Something to remember when generating queries from code

Now you are most likely writing code that will generate your LIKE statements based on a user’s selections. This applies whether you are coding from MS Access or through ASP, ASP.NET, VB, etc… but not if you are running through parameterised objects, for example a .NET 2.0 dataset query.

If you have to code in your SQL you need to remember to change key characters, this means these reserved wildcards as well as the standard single quotes. For example (in VBA): –

SET

myRS = CurrentDB.OpenRecordset( _
    "SELECT * FROM tblContacts WHERE FirstName LIKE ‘" & _
    Replace(someVariable, "’","”",1,-1,1) & "%’", _
    dbOpenDynaset, dbSeeChanges) ‘[WILL NOT WORK]

Though this will work in most situations, if "someVariable" contained any of the wildcard characters, you will experience unexpected results. So you should really do something more like: –

SET

myRS = CurrentDB.OpenRecordset( _
   "SELECT * FROM tblContacts WHERE FirstName LIKE ‘" & _
    Replace( _
     Replace( _
      Replace( _
       Replace( Replace( _
        Replace(someVariable, "’","”",1,-1,1) _
       , "%","[%]",1,-1,1), "#","[#]",1,-1,1) _
      , "[","[[]",1,-1,1) _
     , "]","[]]",1,-1,1) _
    , "_","[_]",1,-1,1) & "%’", _
    dbOpenDynaset, dbSeeChanges)
‘[WILL WORK IF RAN AGAINST SQL LINKED TABLE]

Or if ran against MS Access table or situation described above: –

SET

myRS = CurrentDB.OpenRecordset( _
    "SELECT * FROM tblContacts WHERE FirstName LIKE ‘" & _
     Replace( _
      Replace( _
       Replace( _
        Replace( Replace( _
         Replace(someVariable, "’","”",1,-1,1) _
        , "*","[*]",1,-1,1), "#","[#]",1,-1,1) _
       , "[","[[]",1,-1,1) _
      , "]","[]]",1,-1,1) _
     , "?","[?]",1,-1,1) & "*’", _
    dbOpenDynaset, dbSeeChanges) 
‘[WILL WORK IF RAN AGAINST ACCESS TABLE/QUERY]

As with any form of coding it is usually a good idea to extract out common functions into a small library that can be used throughout your applications.

As such if you do build queries based on user input I’d recommend using a standard function to tidy up any users data and prevent possible conflicts or security flaws. The function could look something like (VBA): –

Function

fnTidyLikeQuery(ByVal myString As Object) As String

        On Error Goto ExitHere
    ‘as it does no harm to escape all characters 
    ‘(SQL or Access specific) this function can 
    ‘take care if both).

    If isNull(myString) Then
        Goto ExitHere
    Else
        fnTidyLikeQuery = _
            Replace( Replace( _
             Replace( Replace( _
               Replace( Replace( _
                Replace( Replace( _
                 myString _
                , "’","”",1,-1,1), "#","[#]",1,-1,1) _
               , "*","[*]",1,-1,1), "[","[[]",1,-1,1) _
              , "%","[%]",1,-1,1), "_","[_]",1,-1,1) _
             , "]","[]]",1,-1,1), "?","[?]",1,-1,1) 
    End If

    Exit Funtion
ExitHere:
    fnTidyLikeQuery = ""
End Function

Function

fnTidyQueryString(byVal myString as Variant) as String

    On Error Goto ExitHere

    If isNull(myString) Then
        Goto ExitHere
    Else
        fnTidyQueryString= Replace(myString, "’","”",1,-1,1) 
    End If

    Exit Funtion
ExitHere:
    fnTidyQueryString = ""
End Function

A sample query based on the above functions would look something like: –

SET

myRS = CurrentDB.OpenRecordset( _
    "SELECT * FROM tblContacts WHERE FirstName LIKE ‘*" & _
    fnTidyLikeQuery(someVariable) & "*’ AND Surname = ‘" & _
    fnTidyQueryString(someOtherVariable) & "’", _
    dbOpenDynaset, dbSeeChanges) 
‘[WILL WORK IF RAN AGAINST ACCESS TABLE/QUERY (uses * as wildcard)]

---

Some more samples of LIKE statements

SQL Clause	Access Equivalent	Result
Like ‘%’	Like ‘*’	All except blanks
Like ‘A%’	Like ‘A*’	Starting with ‘A’
Like ‘%A%’	Like ‘*A*’	‘A’ somewhere in the field
Like ‘[A,B,D]’   or Like ‘[ABD]’	Like ‘[A,B,D]’   or Like ‘[ABD]’	One character an ‘A’ or ‘B’ or ‘D’
Like ‘[A-C]%’	Like ‘[A-C]*’	One character ‘A’ through ‘C’ as the first character
Like ‘[A-C][A-H]%’	Like ‘[A-C][A-H]*’	‘A’ through ‘C’ as the 1st character and ‘A’ through ‘H’ as the 2nd character
Like ‘SM_TH’	Like ‘SM?TH’	Starting with ‘Sm’, ending with ‘th’, and anything for the 3rd character
Like ‘[0-9]%’	Like ‘[0-9]*’   or Like ‘#*’	Digit for the 1st character
Like ‘[^a-c]’	Like ‘[!a-c]’	Not in a range of letters ‘a’ through to ‘c’
Like ‘[^a-c]%’	Like ‘[!a-c]*’	Not start with a range of letters
Like ‘[^0-9]%’	Like ‘[!0-9]*’	Not start with a number