Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006

    Unanswered: SQL parameter Query & wildcard

    I just migrated a stand alone MSAccess2003 .mdb to an .adp Access Data Project. In the former .mdb I had a parameter query to search for a portion of a 17 character part number, primarily the 2 grouping of 4 digits as in (00-0000-XXXX-0-00). In Access the query was written as

    LIKE “*”&[Enter a portion of the part number]&”*”

    Then we ported to a web based portal using MSAccess’s DAP (data access pages) and I was told due to ADO vs DAO the query had to be rewritten using the % symbol as in

    LIKE “%”&[Enter a portion of the part number]&”%”

    So far so good. However with the migration to a SQL Server and ANSI-92 compatible wildcard characters this has changed. The new query looks like this

    LIKE @Enter_a_portion_of_the_part_number +n’%’

    The problem I am having is the prompt for data entry works but the wildcard will only return matching values from the first digit place holder. Some of the books I have read said to use the Under Score character to move the search criteria to the section I want to search which would be starting with the 9th character (00-0000-XXXX-0-00) as in

    LIKE @Enter_a_portion_of_the_part_number +n’________%

    However the Under Score only works with Alpha Characters and not Numeric.

    So how do I write a SQL Server Query to perform like it did in Access?

  2. #2
    Join Date
    Sep 2004
    To match parts of a string with different conditions, it might be helpful to use SUBSTRING() (or similar - the name of this function may depend on the database server).
    E.g., to extract the 'XXXX' from 00-0000-XXXX-0-00, use SUBSTRING('00-0000-XXXX-0-00', 9, 4)

    On the other hand, to check whether a string matches the pattern 00-0000-XXXX-0-00, use the condition
    LIKE '00-0000-____-0-00'
    (Note that this will only match varchar strings without blanks at the end, or strings of type CHAR(17).)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting

  3. #3
    Join Date
    Jun 2003
    Provided Answers: 1
    Use: Like '%' + [YourSubstring] + '%'
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

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