Home Forums Article Discussions Article Discussions by Author Discuss content posted by Manie Verster Patindex RE: Patindex

  • September 15, 2008 at 10:05 am

    #871863

    Carla Wilson (9/15/2008)

    Jeff, thanks for that great test script. I like your code for building 1,000,000 row table. I'm saving this code for reference.

    Thanks for pointing out how much more efficient LEFT is than LIKE or CHARINDEX. (I knew that, but wasn't thinking about it.)

    My original WHERE clause:

    WHERE Address LIKE 'XX%' would be much faster as

    WHERE LEFT(Address,2) = 'XX'

    It's easy to get caught up in

    WHERE Col1 LIKE '%aa%'

    OR Col1 LIKE '%bb%

    OR Col1 LIKE 'xx%'

    Maybe this is where LIKE gets the bad rap - when the code should be using LEFT() instead.

    Yep... from what I can see, LIKE %aa% is faster than either %aa or aa% and LEFT/RIGHT blows that away.

    Thanks for the feedback on the test table. If you have any questions on how it works, please don't hesitate to ask.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)