当前位置：文江博客话题详情

如何使用 html 实体搜索记录集

发布于 2024-10-21 13:57:38 字数 284 浏览 3 评论 0原文

我的数据库中的大多数数据都以 html 实体存储，例如 ä 而不是 ä。但有些数据以纯文本形式存储 (ä)。

现在我想查找带有 ä OR ä 的所有记录集。如何在不使用

SELECT id FROM table WHERE content LIKE '%ä%' OR content LIKE '%ä%' 的情况下最佳地实现此目的

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

可是我不能没有你 2024-10-28 13:57:38

您可以在替换同一查询中要搜索的值的同时搜索表：

SELECT * FROM table WHERE REPLACE(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.

回复收藏 0 原文

旧人 2024-10-28 13:57:38

最佳答案是检查现有数据并将其中一个的所有实例更改为另一个实例以保持一致。

创建一个 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

然后您只需要检查所有包含该批次的表！！！！

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!!!!

回复收藏 0 原文

~没有更多了~