The Strict Replace

Question

The Strict Replace

Steve Jones - SSC Editor

SSC Guru

Points: 736173
More actions
January 14, 2021 at 12:00 am

#3827786

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

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

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

That was an interesting question today! Learned something new!

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

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.

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

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.

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

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 4

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.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 5

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...).

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.