The Strict Replace

  • Comments posted to this topic are about the item The Strict Replace

  • That was an interesting question today!  Learned something new!

  • Mr. Brian Gale wrote:

    That was an interesting question today!  Learned something new!

    As a bit of a sidebar, it will also seriously speed up code for things like REPLACE, especially for servers that don't use the MS default collation because it doesn't have to search for all the different versions of a letter (uppercase v.s. lower case, accent marks, etc, etc).

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Awesome question, Steve!

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden wrote:

    Mr. Brian Gale wrote:

    That was an interesting question today!  Learned something new!

    As a bit of a sidebar, it will also seriously speed up code for things like REPLACE, especially for servers that don't use the MS default collation because it doesn't have to search for all the different versions of a letter (uppercase v.s. lower case, accent marks, etc, etc).

     

    Another fun thing on that note, if you are using a case sensitive or binary database that applies to everything, including object names.

  • ZZartin wrote:

    Jeff Moden wrote:

    Mr. Brian Gale wrote:

    That was an interesting question today!  Learned something new!

    As a bit of a sidebar, it will also seriously speed up code for things like REPLACE, especially for servers that don't use the MS default collation because it doesn't have to search for all the different versions of a letter (uppercase v.s. lower case, accent marks, etc, etc).

    Another fun thing on that note, if you are using a case sensitive or binary database that applies to everything, including object names.

    Thanks for those tips Jeff and ZZartin.  Most of my systems are case insensitive collations so I don't need to worry about that much and most of the time I am doing comparisons or replaces where case doesn't matter.  Well, I shouldn't say "doesn't matter" but the stored case and the case I am searching/replacing is in the format I want.  Never really thought about a REPLACE needing to check all the different characters though when using a CI collation.  Gives me a neat performance boost trick I can use (assuming the column always contains the correct case).

    I also never thought about object names in relation to case sensitivity... but if ANYBODY tried to make a table called "employees" AND a table called "Employees" in my database, I'd be hunting them down pretty quickly.  The ONLY exception to something like that would be if you had different schemas for it, a VALID reason for having the 2 tables named the same thing (something like LocationA.employees and LocationB.Employees, and they hold all employees in the corresponding locations and there would NEVER be need for overlap and there was a need to restrict some of the data access via schemas), and you brought me a coffee that morning so you were in my good books for the day... but even then I'd be pushing for consistency in naming.  Either both are "employees" or both are "Employees".  No inconsistency in my databases (who am I kidding... there's a bunch... stupid technical debt...).

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply