Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    251
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Does anyone know the wildcards of SQL?

    Im looking for the standard wildcards of SQL and what they do... Like '_' replaces a character, but what are the others?

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    here you go (jet and ansi sql)



    ---------info from access helpfile
    Built-in pattern matching provides a versatile tool for making string comparisons. The following table shows the wildcard characters you can use with the Like operator and the number of digits or strings they match.

    Character(s) in pattern Matches in expression
    ? or _ (underscore) Any single character
    * or % Zero or more characters
    # Any single digit (0 — 9)
    [charlist] Any single character in charlist
    [!charlist] Any single character not in charlist


    You can use a group of one or more characters (charlist) enclosed in brackets ([ ]) to match any single character in expression, and charlist can include almost any characters in the ANSI character set, including digits. You can use the special characters opening bracket ([ ), question mark (?), number sign (#), and asterisk (*) to match themselves directly only if enclosed in brackets. You cannot use the closing bracket ( ]) within a group to match itself, but you can use it outside a group as an individual character.

    In addition to a simple list of characters enclosed in brackets, charlist can specify a range of characters by using a hyphen (-) to separate the upper and lower bounds of the range. For example, using [A-Z] in pattern results in a match if the corresponding character position in expression contains any of the uppercase letters in the range A through Z. You can include multiple ranges within the brackets without delimiting the ranges. For example, [a-zA-Z0-9] matches any alphanumeric character.

    It is important to note that the ANSI SQL wildcards (%) and (_) are only available with Microsoft® Jet version 4.X and the Microsoft OLE DB Provider for Jet. They will be treated as literals if used through Microsoft Access or DAO.

    Other important rules for pattern matching include the following:

    An exclamation mark (!) at the beginning of charlist means that a match is made if any character except those in charlist are found in expression. When used outside brackets, the exclamation mark matches itself.
    You can use the hyphen (-) either at the beginning (after an exclamation mark if one is used) or at the end of charlist to match itself. In any other location, the hyphen identifies a range of ANSI characters.
    When you specify a range of characters, the characters must appear in ascending sort order (A-Z or 0-100). [A-Z] is a valid pattern, but [Z-A] is not.
    The character sequence [ ] is ignored; it is considered to be a zero-length string (“”).

  • #3
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Maybe completely irrelevant, and certainly shows my level of experience but - something I only found out today:

    DAO recognises '*' as a wildcard.
    ADO recognises '%' as a wildcard.

    So, if you've built up your SQL string in Access (which uses DAO) and then stick it in your ASP page (which talks to Access via ADO), you'll get all sorts of amusing errors which will keep you entertained for hours until you decide that Thursdays were never a good day anyway, and you'd be much better off in the pub.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •