Geek asks and answers

How to search record sets with html objects

Most of the data in my database is stored with html objects, for example, äinstead ä. But some data is stored as plain text ( ä).

Now I want to find all record sets using äOR & auml ;. How can I optimally achieve this without using

SELECT id FROM table WHERE content LIKE '%% ä' OR LIKE content '%% ä'

+3
source share
2 answers

You can search the table by replacing the values ​​to search in the same query:

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

Of course, you will have to do a replacement for all Umlaute.

+1
source

- , .

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

!!!!

0

More articles:


All Articles