Learning Every Day: Case Sensitive Replace

During some T-SQL programming I needed to add spaces before some characters within a string.
The problem was that characters were in upper and lover case and I had to preserve their cases.

Idea for the solution was found in "stackoverflow.com".

Here is how it can be done:

SELECT REPLACE('1C2c3' COLLATE Latin1_General_CS_AS, 'C', ' C');
SELECT REPLACE('4C5c6' COLLATE Latin1_General_CS_AS, 'c', ' c');

And here is the result:

--------
1 C2c3
--------
4C5 c6

The trick is in using "Latin1_General_CS_AS" case sensitive collation to do the replace.

You can also use that method for a search. For example two queries below will return different results:

SELECT CHARINDEX('C', '1C2c3' COLLATE Latin1_General_CS_AS);
SELECT CHARINDEX('c', '1C2c3' COLLATE Latin1_General_CS_AS);