How to search recordsets with html entities

Most data in my database is stored with html entities, such as ä instead of ä. But some data is stored as plain text (ä).

Now I want to find all recordsets with ä OR ä. How can I optimally achieve this without using

SELECT id F开发者_开发技巧ROM table WHERE content LIKE '%ä%' OR content LIKE '%ä%'

You can search the table while replacing the values to be searched in the same query:

SELECT * FROM table WHERE REPLACE(content,'ä','ä') LIKE '%ä%'

You'll have to do the replace for all Umlaute of course.

Best answer is to go through your existing data and change all instances of the one to the other to be consistent.

Create a UDF

Create Function dbo.ReplaceHtmlEntities(@arg NVARCHAR(MAX) collate Latin1_General_Bin)
returns NVARCHAR(MAX) 
as
begin
   if @arg is null return @arg
   if not @arg like '%&%;%' return @arg
   -- Collation matters here obviously!!
   -- Auto generated lines
   -- These lines should be generated from a list of entities and Unicode values
   -- In practice you can limit this to the ones you actually have a problem with 
   set @arg = replace(@arg, 'Ä' collate Latin1_General_BIN, char(0xUUUU))
   set @arg = replace(@arg, 'ä' collate Latin1_General_BIN, char(0xUUUU))
   set @arg = replace(@arg, 'Ö' collate Latin1_General_BIN, char(0xUUUU))
   set @arg = replace(@arg, 'ö' collate Latin1_General_BIN, char(0xUUUU))
   -- For speed you can group them more common first, and short-circuit where possible
   if not @arg like '%&%;%' return @arg

   -- a lot more lines.... 
   return @arg
end

Then you just need to go through all your tables with that lot!!!!