Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Like v. Instr (any)

    Does anyone have any evidence that supports using Like over Instr?
    Specifically, is Like faster than Instr or vice versa or does it matter?

    e.g.

    If aEntry.Name like "Bob*" then
    msgbox "Hi Bob."
    endif

    v.

    If instr(aEntry.Name,"Bob") <> 0 then
    msgbox "Hi Bob."
    endif
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Like v. Instr (any)

    In simple cases yes, but Isn't it rather a pain to create InStr equivalents to LIKE's other wildcards and wildcard combinations?

    ? Any single character.
    # Any single digit (0
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like v. Instr (any)

    John,
    Absolutely. I like Like, but swayed the other way due to vague reports about
    Like's slower performance.

    I just did a google search and found the performance issue mentioned, but
    at 50,000 plus iterations. Not really a concern.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Like v. Instr (any)

    The Instr() function returns a numeric value for the position at which the first match is found and allows you to specify the starting point for the test. If you don't need the return value, why use Instr? Like is an operator rather than a function and really has a different purpose, since you don't care where the match is located, only whether it exists. Frankly, in most cases, such as your example, I would opt for Like simply because it is so much more readable and you don't have any specific use for the InStr return value. On the other hand, If you need a match in a specific location, then Like won't get you there and you'll have to resort to Instr. I would choose the most appropriate tool for the need and not worry about minor speed differences. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like v. Instr (any)

    >>so much more readable

    You nailed it!
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  6. #6
    Star Lounger
    Join Date
    Aug 2001
    Location
    GA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like v. Instr (any)

    sqlmag.com had an artical with this to say:
    In terms of optimizing queries, the LIKE operator can be as inefficient as NOT. If you have a clause such as WHERE LastName LIKE '%Mc%, SQL Server performs a pattern search on the entire column. An index won't help, so you can expect the query optimizer to choose a table scan. The one exception happens when the clause looks like WHERE LastName LIKE 'Le%'. The difference is that this search is logically equivalent to WHERE LastName >= 'Le' AND LastName < 'LF', which is a search argument.


    For those who 'like' Like...
    Susan Harkins wrote an artical for WAW, it covered cool ways to use the Like operator.
    Whats not to like about Like?

    Later,
    bruce

Posting Permissions

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