Doing a case sensitive REPLACE in T-Sql

Oh, the wonder of Scandinavian letters (and German, and French, and Dutch) … Well, I’ve just had to do a case sensitive text replacement on my Sql-box, which uses the otherwise case insensitive collation “Danish_Norwegian_CI_AS”.

Fortunately the REPLACE function can take a collation as part of its string expression so my letter substitution ended up like so:

	UPDATE [OEDBAdmin].[dbo].[KontrolRapporter] SET Navn = 
		REPLACE(
			REPLACE(
				REPLACE(
					REPLACE(
						REPLACE(
							REPLACE(
								REPLACE(
									Navn COLLATE Danish_Norwegian_CS_AS,'øe','oe'),
									'æ' COLLATE Danish_Norwegian_CS_AS,'ae'),
									'ø' COLLATE Danish_Norwegian_CS_AS,'oe'),
									'å' COLLATE Danish_Norwegian_CS_AS,'aa'),
									'Æ' COLLATE Danish_Norwegian_CS_AS,'Ae'),
									'Ø' COLLATE Danish_Norwegian_CS_AS,'Oe'),
									'Å' COLLATE Danish_Norwegian_CS_AS,'Aa')