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:
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);