我有这个示例表：
表1
Column1
-------
Hi&Hello
Hello & Hi
Snacks & Drinks
Hello World
问：在 SQL Server 中，如何在不影响现有字符实体的情况下将所有&替换为字符实体(&(？
谢谢！
- 如果你真的想在你的数据库中替换它，你可以尝试运行
UPDATE Table1 SET Column1 = REPLACE(Column1, '&', '&');
- 我想你想这样做是因为你想在数据库中以完全相同的方式在网站上显示数据。因此，我建议您在显示它时(在应用程序端(进行转义，因为当您的数据库中有大量
&或 时，维护起来并不容易。
例如：
在 php 中，您可以使用htmlspecialchars();
在 Java 中，您可以import static org.apache.commons.lang.StringEscapeUtils.escapeHtml;然后使用escapeHtml();
在 Ruby on rails 中，您可以使用 HTMLEntities.new.encode();(如果您使用 rails3 或更高版本，默认情况下应进行转义。
我假设您询问的原因是因为您拥有的不仅仅是 字符实体 - 我假设它超过 5 或 6，因为如果它是一个很小的数字，简单的答案是为每个字符实体运行一个更新语句，将开头的 & 符号替换为列中不存在的字符组合(例如 |#|#|#(。这将需要子字符串等。然后将所有与号更新为 &.然后运行另一个更新语句，将特殊字符组合替换为 & 符号。大功告成。
假设它是更多不同的字符集，这是一次性清理吗？如果不是，如果这是您希望进行的事情，为什么系统将 & 符号存储为只是 & 符号，而将其他东西存储为字符实体？我会先解决这个问题。如果您无法控制这一点，那么我的解决方案可能仍然有效，但需要进一步优化。
如果是一次性清理，我认为您需要创建一个临时使用的表，其中包含数据库中可能拥有的所有字符实体的列表。您可以在谷歌上搜索HTML字符实体并找到列表，通过剪切和粘贴，可能会使您在不到30分钟的时间内生成此表。
一旦你有了这张桌子，那么你的道路就相当清晰了。就像其他人说的那样，您可能不希望像这样将编码数据存储在数据库中。在这种情况下，您只需编写一个游标并循环遍历使用 replace 语句进行更新的新字符实体表。但是，如果您真的只想替换与号，则可以遍历光标，并将每个字符实体开头的与号替换为列中永远不会存在的字符组合，例如 |#|#|#。之后，数据库中唯一剩下的 & 符号应该是实际的 & 符号，而不是字符实体的一部分。因此，它与我在第一段中提到的最后两个步骤相同：运行单个查询将所有与号更新为 &。然后运行另一个更新语句，将特殊的字符组合替换为实际的 & 符号。
--https://www.w3.org/TR/xml-entity-names/bycodes.html
UPDATE Items SET Name = REPLACE(Name, '&', NCHAR(38))
UPDATE Items SET Name = REPLACE(Name, '<', NCHAR(60))
UPDATE Items SET Name = REPLACE(Name, '>', NCHAR(62))
UPDATE Items SET Name = REPLACE(Name, 'À', NCHAR(192))
UPDATE Items SET Name = REPLACE(Name, 'Á', NCHAR(193))
UPDATE Items SET Name = REPLACE(Name, 'Â', NCHAR(194))
UPDATE Items SET Name = REPLACE(Name, 'Ã', NCHAR(195))
UPDATE Items SET Name = REPLACE(Name, 'Ä', NCHAR(196))
UPDATE Items SET Name = REPLACE(Name, 'Å', NCHAR(197))
UPDATE Items SET Name = REPLACE(Name, 'Æ', NCHAR(198))
UPDATE Items SET Name = REPLACE(Name, 'Ç', NCHAR(199))
UPDATE Items SET Name = REPLACE(Name, 'È', NCHAR(200))
UPDATE Items SET Name = REPLACE(Name, 'É', NCHAR(201))
UPDATE Items SET Name = REPLACE(Name, 'Ê', NCHAR(202))
UPDATE Items SET Name = REPLACE(Name, 'Ë', NCHAR(203))
UPDATE Items SET Name = REPLACE(Name, 'Ì', NCHAR(204))
UPDATE Items SET Name = REPLACE(Name, 'Í', NCHAR(205))
UPDATE Items SET Name = REPLACE(Name, 'Î', NCHAR(206))
UPDATE Items SET Name = REPLACE(Name, 'Ï', NCHAR(207))
UPDATE Items SET Name = REPLACE(Name, 'Ð', NCHAR(208))
UPDATE Items SET Name = REPLACE(Name, 'Ñ', NCHAR(209))
UPDATE Items SET Name = REPLACE(Name, 'Ò', NCHAR(210))
UPDATE Items SET Name = REPLACE(Name, 'Ó', NCHAR(211))
UPDATE Items SET Name = REPLACE(Name, 'Ô', NCHAR(212))
UPDATE Items SET Name = REPLACE(Name, 'Õ', NCHAR(213))
UPDATE Items SET Name = REPLACE(Name, 'Ö', NCHAR(214))
UPDATE Items SET Name = REPLACE(Name, 'Ø', NCHAR(216))
UPDATE Items SET Name = REPLACE(Name, 'Ù', NCHAR(217))
UPDATE Items SET Name = REPLACE(Name, 'Ú', NCHAR(218))
UPDATE Items SET Name = REPLACE(Name, 'Û', NCHAR(219))
UPDATE Items SET Name = REPLACE(Name, 'Ü', NCHAR(220))
UPDATE Items SET Name = REPLACE(Name, 'Ý', NCHAR(221))
UPDATE Items SET Name = REPLACE(Name, 'Þ', NCHAR(222))
UPDATE Items SET Name = REPLACE(Name, 'ß', NCHAR(223))
UPDATE Items SET Name = REPLACE(Name, 'à', NCHAR(224))
UPDATE Items SET Name = REPLACE(Name, 'á', NCHAR(225))
UPDATE Items SET Name = REPLACE(Name, 'â', NCHAR(226))
UPDATE Items SET Name = REPLACE(Name, 'ã', NCHAR(227))
UPDATE Items SET Name = REPLACE(Name, 'ä', NCHAR(228))
UPDATE Items SET Name = REPLACE(Name, 'å', NCHAR(229))
UPDATE Items SET Name = REPLACE(Name, 'æ', NCHAR(230))
UPDATE Items SET Name = REPLACE(Name, 'ç', NCHAR(231))
UPDATE Items SET Name = REPLACE(Name, 'è', NCHAR(232))
UPDATE Items SET Name = REPLACE(Name, 'é', NCHAR(233))
UPDATE Items SET Name = REPLACE(Name, 'ê', NCHAR(234))
UPDATE Items SET Name = REPLACE(Name, 'ë', NCHAR(235))
UPDATE Items SET Name = REPLACE(Name, 'ì', NCHAR(236))
UPDATE Items SET Name = REPLACE(Name, 'í', NCHAR(237))
UPDATE Items SET Name = REPLACE(Name, 'î', NCHAR(238))
UPDATE Items SET Name = REPLACE(Name, 'ï', NCHAR(239))
UPDATE Items SET Name = REPLACE(Name, 'ð', NCHAR(240))
UPDATE Items SET Name = REPLACE(Name, 'ñ', NCHAR(241))
UPDATE Items SET Name = REPLACE(Name, 'ò', NCHAR(242))
UPDATE Items SET Name = REPLACE(Name, 'ó', NCHAR(243))
UPDATE Items SET Name = REPLACE(Name, 'ô', NCHAR(244))
UPDATE Items SET Name = REPLACE(Name, 'õ', NCHAR(245))
UPDATE Items SET Name = REPLACE(Name, 'ö', NCHAR(246))
UPDATE Items SET Name = REPLACE(Name, 'ø', NCHAR(248))
UPDATE Items SET Name = REPLACE(Name, 'ù', NCHAR(249))
UPDATE Items SET Name = REPLACE(Name, 'ú', NCHAR(250))
UPDATE Items SET Name = REPLACE(Name, 'û', NCHAR(251))
UPDATE Items SET Name = REPLACE(Name, 'ü', NCHAR(252))
UPDATE Items SET Name = REPLACE(Name, 'ý', NCHAR(253))
UPDATE Items SET Name = REPLACE(Name, 'þ', NCHAR(254))
UPDATE Items SET Name = REPLACE(Name, 'ÿ', NCHAR(255))
UPDATE Items SET Name = REPLACE(Name, ' ', NCHAR(160))
UPDATE Items SET Name = REPLACE(Name, '¡', NCHAR(161))
UPDATE Items SET Name = REPLACE(Name, '¢', NCHAR(162))
UPDATE Items SET Name = REPLACE(Name, '£', NCHAR(163))
UPDATE Items SET Name = REPLACE(Name, '¤', NCHAR(164))
UPDATE Items SET Name = REPLACE(Name, '¥', NCHAR(165))
UPDATE Items SET Name = REPLACE(Name, '¦', NCHAR(166))
UPDATE Items SET Name = REPLACE(Name, '§', NCHAR(167))
UPDATE Items SET Name = REPLACE(Name, '¨', NCHAR(168))
UPDATE Items SET Name = REPLACE(Name, '©', NCHAR(169))
UPDATE Items SET Name = REPLACE(Name, 'ª', NCHAR(170))
UPDATE Items SET Name = REPLACE(Name, '«', NCHAR(171))
UPDATE Items SET Name = REPLACE(Name, '¬', NCHAR(172))
UPDATE Items SET Name = REPLACE(Name, '­', NCHAR(173))
UPDATE Items SET Name = REPLACE(Name, '®', NCHAR(174))
UPDATE Items SET Name = REPLACE(Name, '¯', NCHAR(175))
UPDATE Items SET Name = REPLACE(Name, '°', NCHAR(176))
UPDATE Items SET Name = REPLACE(Name, '±', NCHAR(177))
UPDATE Items SET Name = REPLACE(Name, '²', NCHAR(178))
UPDATE Items SET Name = REPLACE(Name, '³', NCHAR(179))
UPDATE Items SET Name = REPLACE(Name, '´', NCHAR(180))
UPDATE Items SET Name = REPLACE(Name, 'µ', NCHAR(181))
UPDATE Items SET Name = REPLACE(Name, '¶', NCHAR(182))
UPDATE Items SET Name = REPLACE(Name, '¸', NCHAR(184))
UPDATE Items SET Name = REPLACE(Name, '¹', NCHAR(185))
UPDATE Items SET Name = REPLACE(Name, 'º', NCHAR(186))
UPDATE Items SET Name = REPLACE(Name, '»', NCHAR(187))
UPDATE Items SET Name = REPLACE(Name, '¼', NCHAR(188))
UPDATE Items SET Name = REPLACE(Name, '½', NCHAR(189))
UPDATE Items SET Name = REPLACE(Name, '¾', NCHAR(190))
UPDATE Items SET Name = REPLACE(Name, '¿', NCHAR(191))
UPDATE Items SET Name = REPLACE(Name, '×', NCHAR(215))
UPDATE Items SET Name = REPLACE(Name, '÷', NCHAR(247))
UPDATE Items SET Name = REPLACE(Name, '∀', NCHAR(8704))
UPDATE Items SET Name = REPLACE(Name, '∂', NCHAR(8706))
UPDATE Items SET Name = REPLACE(Name, '∃', NCHAR(8707))
UPDATE Items SET Name = REPLACE(Name, '∅', NCHAR(8709))
UPDATE Items SET Name = REPLACE(Name, '∇', NCHAR(8711))
UPDATE Items SET Name = REPLACE(Name, '∈', NCHAR(8712))
UPDATE Items SET Name = REPLACE(Name, '∉', NCHAR(8713))
UPDATE Items SET Name = REPLACE(Name, '∋', NCHAR(8715))
UPDATE Items SET Name = REPLACE(Name, '∏', NCHAR(8719))
UPDATE Items SET Name = REPLACE(Name, '∑', NCHAR(8721))
UPDATE Items SET Name = REPLACE(Name, '−', NCHAR(8722))
UPDATE Items SET Name = REPLACE(Name, '∗', NCHAR(8727))
UPDATE Items SET Name = REPLACE(Name, '√', NCHAR(8730))
UPDATE Items SET Name = REPLACE(Name, '∝', NCHAR(8733))
UPDATE Items SET Name = REPLACE(Name, '∞', NCHAR(8734))
UPDATE Items SET Name = REPLACE(Name, '∠', NCHAR(8736))
UPDATE Items SET Name = REPLACE(Name, '∧', NCHAR(8743))
UPDATE Items SET Name = REPLACE(Name, '∨', NCHAR(8744))
UPDATE Items SET Name = REPLACE(Name, '∩', NCHAR(8745))
UPDATE Items SET Name = REPLACE(Name, '∪', NCHAR(8746))
UPDATE Items SET Name = REPLACE(Name, '∫', NCHAR(8747))
UPDATE Items SET Name = REPLACE(Name, '∴', NCHAR(8756))
UPDATE Items SET Name = REPLACE(Name, '∼', NCHAR(8764))
UPDATE Items SET Name = REPLACE(Name, '≅', NCHAR(8773))
UPDATE Items SET Name = REPLACE(Name, '≈', NCHAR(8776))
UPDATE Items SET Name = REPLACE(Name, '≠', NCHAR(8800))
UPDATE Items SET Name = REPLACE(Name, '≡', NCHAR(8801))
UPDATE Items SET Name = REPLACE(Name, '≤', NCHAR(8804))
UPDATE Items SET Name = REPLACE(Name, '≥', NCHAR(8805))
UPDATE Items SET Name = REPLACE(Name, '⊂', NCHAR(8834))
UPDATE Items SET Name = REPLACE(Name, '⊃', NCHAR(8835))
UPDATE Items SET Name = REPLACE(Name, '⊄', NCHAR(8836))
UPDATE Items SET Name = REPLACE(Name, '⊆', NCHAR(8838))
UPDATE Items SET Name = REPLACE(Name, '⊇', NCHAR(8839))
UPDATE Items SET Name = REPLACE(Name, '⊕', NCHAR(8853))
UPDATE Items SET Name = REPLACE(Name, '⊗', NCHAR(8855))
UPDATE Items SET Name = REPLACE(Name, '⊥', NCHAR(8869))
UPDATE Items SET Name = REPLACE(Name, '⋅', NCHAR(8901))
UPDATE Items SET Name = REPLACE(Name, 'Α', NCHAR(913))
UPDATE Items SET Name = REPLACE(Name, 'Β', NCHAR(914))
UPDATE Items SET Name = REPLACE(Name, 'Γ', NCHAR(915))
UPDATE Items SET Name = REPLACE(Name, 'Δ', NCHAR(916))
UPDATE Items SET Name = REPLACE(Name, 'Ε', NCHAR(917))
UPDATE Items SET Name = REPLACE(Name, 'Ζ', NCHAR(918))
UPDATE Items SET Name = REPLACE(Name, 'Η', NCHAR(919))
UPDATE Items SET Name = REPLACE(Name, 'Θ', NCHAR(920))
UPDATE Items SET Name = REPLACE(Name, 'Ι', NCHAR(921))
UPDATE Items SET Name = REPLACE(Name, 'Κ', NCHAR(922))
UPDATE Items SET Name = REPLACE(Name, 'Λ', NCHAR(923))
UPDATE Items SET Name = REPLACE(Name, 'Μ', NCHAR(924))
UPDATE Items SET Name = REPLACE(Name, 'Ν', NCHAR(925))
UPDATE Items SET Name = REPLACE(Name, 'Ξ', NCHAR(926))
UPDATE Items SET Name = REPLACE(Name, 'Ο', NCHAR(927))
UPDATE Items SET Name = REPLACE(Name, 'Π', NCHAR(928))
UPDATE Items SET Name = REPLACE(Name, 'Ρ', NCHAR(929))
UPDATE Items SET Name = REPLACE(Name, 'Σ', NCHAR(931))
UPDATE Items SET Name = REPLACE(Name, 'Τ', NCHAR(932))
UPDATE Items SET Name = REPLACE(Name, 'Υ', NCHAR(933))
UPDATE Items SET Name = REPLACE(Name, 'Φ', NCHAR(934))
UPDATE Items SET Name = REPLACE(Name, 'Χ', NCHAR(935))
UPDATE Items SET Name = REPLACE(Name, 'Ψ', NCHAR(936))
UPDATE Items SET Name = REPLACE(Name, 'Ω', NCHAR(937))
UPDATE Items SET Name = REPLACE(Name, 'α', NCHAR(945))
UPDATE Items SET Name = REPLACE(Name, 'β', NCHAR(946))
UPDATE Items SET Name = REPLACE(Name, 'γ', NCHAR(947))
UPDATE Items SET Name = REPLACE(Name, 'δ', NCHAR(948))
UPDATE Items SET Name = REPLACE(Name, 'ε', NCHAR(949))
UPDATE Items SET Name = REPLACE(Name, 'ζ', NCHAR(950))
UPDATE Items SET Name = REPLACE(Name, 'η', NCHAR(951))
UPDATE Items SET Name = REPLACE(Name, 'θ', NCHAR(952))
UPDATE Items SET Name = REPLACE(Name, 'ι', NCHAR(953))
UPDATE Items SET Name = REPLACE(Name, 'κ', NCHAR(954))
UPDATE Items SET Name = REPLACE(Name, 'λ', NCHAR(955))
UPDATE Items SET Name = REPLACE(Name, 'μ', NCHAR(956))
UPDATE Items SET Name = REPLACE(Name, 'ν', NCHAR(957))
UPDATE Items SET Name = REPLACE(Name, 'ξ', NCHAR(958))
UPDATE Items SET Name = REPLACE(Name, 'ο', NCHAR(959))
UPDATE Items SET Name = REPLACE(Name, 'π', NCHAR(960))
UPDATE Items SET Name = REPLACE(Name, 'ρ', NCHAR(961))
UPDATE Items SET Name = REPLACE(Name, 'ς', NCHAR(962))
UPDATE Items SET Name = REPLACE(Name, 'σ', NCHAR(963))
UPDATE Items SET Name = REPLACE(Name, 'τ', NCHAR(964))
UPDATE Items SET Name = REPLACE(Name, 'υ', NCHAR(965))
UPDATE Items SET Name = REPLACE(Name, 'φ', NCHAR(966))
UPDATE Items SET Name = REPLACE(Name, 'χ', NCHAR(967))
UPDATE Items SET Name = REPLACE(Name, 'ψ', NCHAR(968))
UPDATE Items SET Name = REPLACE(Name, 'ω', NCHAR(969))
UPDATE Items SET Name = REPLACE(Name, 'ϑ', NCHAR(977))
UPDATE Items SET Name = REPLACE(Name, 'ϒ', NCHAR(978))
UPDATE Items SET Name = REPLACE(Name, 'ϖ', NCHAR(982))
UPDATE Items SET Name = REPLACE(Name, 'Œ', NCHAR(338))
UPDATE Items SET Name = REPLACE(Name, 'œ', NCHAR(339))
UPDATE Items SET Name = REPLACE(Name, 'Š', NCHAR(352))
UPDATE Items SET Name = REPLACE(Name, 'š', NCHAR(353))
UPDATE Items SET Name = REPLACE(Name, 'Ÿ', NCHAR(376))
UPDATE Items SET Name = REPLACE(Name, 'ƒ', NCHAR(402))
UPDATE Items SET Name = REPLACE(Name, 'ˆ', NCHAR(710))
UPDATE Items SET Name = REPLACE(Name, '˜', NCHAR(732))
UPDATE Items SET Name = REPLACE(Name, ' ', NCHAR(8194))
UPDATE Items SET Name = REPLACE(Name, ' ', NCHAR(8195))
UPDATE Items SET Name = REPLACE(Name, ' ', NCHAR(8201))
UPDATE Items SET Name = REPLACE(Name, '‌', NCHAR(8204))
UPDATE Items SET Name = REPLACE(Name, '‍', NCHAR(8205))
UPDATE Items SET Name = REPLACE(Name, '‎', NCHAR(8206))
UPDATE Items SET Name = REPLACE(Name, '‏', NCHAR(8207))
UPDATE Items SET Name = REPLACE(Name, '–', NCHAR(8211))
UPDATE Items SET Name = REPLACE(Name, '—', NCHAR(8212))
UPDATE Items SET Name = REPLACE(Name, '‘', NCHAR(8216))
UPDATE Items SET Name = REPLACE(Name, '’', NCHAR(8217))
UPDATE Items SET Name = REPLACE(Name, '‚', NCHAR(8218))
UPDATE Items SET Name = REPLACE(Name, '“', NCHAR(8220))
UPDATE Items SET Name = REPLACE(Name, '”', NCHAR(8221))
UPDATE Items SET Name = REPLACE(Name, '„', NCHAR(8222))
UPDATE Items SET Name = REPLACE(Name, '†', NCHAR(8224))
UPDATE Items SET Name = REPLACE(Name, '‡', NCHAR(8225))
UPDATE Items SET Name = REPLACE(Name, '•', NCHAR(8226))
UPDATE Items SET Name = REPLACE(Name, '…', NCHAR(8230))
UPDATE Items SET Name = REPLACE(Name, '‰', NCHAR(8240))
UPDATE Items SET Name = REPLACE(Name, '′', NCHAR(8242))
UPDATE Items SET Name = REPLACE(Name, '″', NCHAR(8243))
UPDATE Items SET Name = REPLACE(Name, '‹', NCHAR(8249))
UPDATE Items SET Name = REPLACE(Name, '›', NCHAR(8250))
UPDATE Items SET Name = REPLACE(Name, '‾', NCHAR(8254))
UPDATE Items SET Name = REPLACE(Name, '€', NCHAR(8364))
UPDATE Items SET Name = REPLACE(Name, '™', NCHAR(8482))
UPDATE Items SET Name = REPLACE(Name, '←', NCHAR(8592))
UPDATE Items SET Name = REPLACE(Name, '↑', NCHAR(8593))
UPDATE Items SET Name = REPLACE(Name, '→', NCHAR(8594))
UPDATE Items SET Name = REPLACE(Name, '↓', NCHAR(8595))
UPDATE Items SET Name = REPLACE(Name, '↔', NCHAR(8596))
UPDATE Items SET Name = REPLACE(Name, '↵', NCHAR(8629))
UPDATE Items SET Name = REPLACE(Name, '⌈', NCHAR(8968))
UPDATE Items SET Name = REPLACE(Name, '⌉', NCHAR(8969))
UPDATE Items SET Name = REPLACE(Name, '⌊', NCHAR(8970))
UPDATE Items SET Name = REPLACE(Name, '⌋', NCHAR(8971))
UPDATE Items SET Name = REPLACE(Name, '◊', NCHAR(9674))
UPDATE Items SET Name = REPLACE(Name, '♠', NCHAR(9824))
UPDATE Items SET Name = REPLACE(Name, '♣', NCHAR(9827))
UPDATE Items SET Name = REPLACE(Name, '♥', NCHAR(9829))
UPDATE Items SET Name = REPLACE(Name, '♦', NCHAR(9830))