using Like operator


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: using Like operator

  1. #1
    nin Guest

    using Like operator


    I want to list all possible people with a name coming from a parameter.
    this is what i have, but i dont know how the exact syntax goes.

    WHERE [contact name] Like FACILITY.Contact;
    //this wants a precise match, which i dont want
    //can i use wildcard characters to append to the record field?

    //i want something broader, like if the user enters chris, it will list
    christopher
    chris
    chris1
    john chris cruize
    ...


    thanks guys

  2. #2
    Arthur Wood Guest

    Re: using Like operator


    nin,
    When you use the Like operator, you also supply a 'wildcard' in the query:

    Using DAO, thge wildcard character is the *, while with ADO (and all other
    standard SQL drivers) the wildcard character is the %:

    so your where clause would look like this:

    "WHERE [contact name] Like '*" & FACILITY.Contact & "*'"

    or

    "WHERE [contact name] Like '%" & FACILITY.Contact & "%'"



    which would then get a hit when the text in Facility.Contact was found anywhere
    in the Contact Name field.



    "WHERE [contact name] Like '*" & FACILITY.Contact & "'"

    would find it at the RIGHT end of the field, while:


    "WHERE [contact name] Like '" & FACILITY.Contact & "*'"

    would find it at the LEFT end of the field.


    Arthur Wood


    "nin" <ninteck@hotmail.com> wrote:
    >
    >I want to list all possible people with a name coming from a parameter.
    >this is what i have, but i dont know how the exact syntax goes.
    >
    >WHERE [contact name] Like FACILITY.Contact;
    >//this wants a precise match, which i dont want
    >//can i use wildcard characters to append to the record field?
    >
    >//i want something broader, like if the user enters chris, it will list
    >christopher
    >chris
    >chris1
    >john chris cruize
    >...
    >
    >
    >thanks guys



  3. #3
    Joe \Nuke Me Xemu\ Foster Guest

    Re: using Like operator

    "nin" <ninteck@hotmail.com> wrote in message <news:3d791674$1@10.1.10.29>...

    > I want to list all possible people with a name coming from a parameter.
    > this is what i have, but i dont know how the exact syntax goes.
    >
    > WHERE [contact name] Like FACILITY.Contact;
    > //this wants a precise match, which i dont want
    > //can i use wildcard characters to append to the record field?
    >
    > //i want something broader, like if the user enters chris, it will list
    > christopher
    > chris
    > chris1
    > john chris cruize
    > ..


    Should entering "Anna" list "Joanna"? If so,

    DAO:
    WHERE FACILITY.Contact Like '*' & [contact name] & '*'

    ADO:
    WHERE FACILITY.Contact Like '%' & [contact name] & '%'

    Expect poor performance, even if an index exists on FACILITY.Contact.

    --
    Joe Foster <mailto:jlfoster%40znet.com> DC8s in Spaace: <http://www.xenu.net/>
    WARNING: I cannot be held responsible for the above They're coming to
    because my cats have apparently learned to type. take me away, ha ha!



  4. #4
    Roman Victor Guest

    Re: using Like operator


    Nin,

    Try using the * or % wild card. Ruomr has it that * works better with Access.

    Like Operator

    Compares a string expression to a pattern in an SQL expression.

    Syntax expression Like “pattern”

    The Like operator syntax has these parts:

    Part Description
    expression SQL expression used in a WHERE clause.
    pattern String or character string literal against which expression is compared.



    Remarks
    You can use the Like operator to find values in a field that match the pattern
    you specify. For pattern, you can specify the complete value (for example,
    Like “Smith”), or you can use wildcard characters to find a range of values
    (for example, Like “Sm*”).

    In an expression, you can use the Like operator to compare a field value
    to a string expression. For example, if you enter Like “C*” in an SQL query,
    the query returns all field values beginning with the letter C. In a parameter
    query, you can prompt the user for a pattern to search for.

    The following example returns data that begins with the letter P followed
    by any letter between A and F and three digits:

    Like “P[A-F]###”


    The following table shows how you can use Like to test expressions for different
    patterns.


    Kind of match Pattern Match (returns True) No match (returns False)
    Multiple characters a*a aa, aBa, aBBBa aBC *ab* abc, AABB, Xab aZb, bac
    Special character a[*]a a*a aaa
    Multiple characters ab* abcdefg, abc cab, aab
    Single character a?a aaa, a3a, aBa aBBBa
    Single digit a#a a0a, a1a, a2a aaa, a10a
    Range of characters [a-z] f, p, j 2, &
    Outside a range [!a-z] 9, &, % b, a
    Not a digit [!0-9] A, a, &, ~ 0, 1, 9
    Combined a[!b-m]# An9, az0, a99 abc, aj0


    "nin" <ninteck@hotmail.com> wrote:
    >
    >I want to list all possible people with a name coming from a parameter.
    >this is what i have, but i dont know how the exact syntax goes.
    >
    >WHERE [contact name] Like FACILITY.Contact;
    >//this wants a precise match, which i dont want
    >//can i use wildcard characters to append to the record field?
    >
    >//i want something broader, like if the user enters chris, it will list
    >christopher
    >chris
    >chris1
    >john chris cruize
    >...
    >
    >
    >thanks guys



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center