SQL Server : replace "&" to character entities (&)

Question

I have this sample table:

Table1

Column1
-------
Hi&Hello
Hello & Hi
Snacks & Drinks
   Hello World  

Question: in SQL Server, how can I replace all the & into a character entities (&) without affecting the existing character entities?

Thanks!

Answer 1

1. If you really want to replace it in your database, you can try running

    UPDATE Table1 SET Column1 = REPLACE(Column1, '&', '&');

2. I suppose you want to do this because you want to display data on the site exactly the same way in your database. So I suggest you to escape when you display it (in the application side) since it is not easy to maintain when you have lots of & or   in your database.

For example:

In php, you can use htmlspecialchars();

In java, you can import static org.apache.commons.lang.StringEscapeUtils.escapeHtml; and then use escapeHtml();

In ruby on rails, you can use HTMLEntities.new.encode(); (If you use rails3 or newer version, escaping should be done by default.)

Answer 2

I am assuming that the reason you are asking is because it's not just   character entities you have - I'm assuming it's more than 5 or 6, because if it's that small a number, the easy answer is run an update statement for each character entity replacing the ampersand at the beginning of it with some combination of characters that doesn't exist in the column (e.g. |#|#|#). This will require substring among other things. Then update all the ampersands to &. Then run another update statement replacing your special combination of characters with ampersands. You're done.

Assuming it's more different character sets, is this a one time cleanup? If not, if it's something you expect to be ongoing, why is the system storing ampersands as just ampersands but other things as character entities? I'd attack that problem first. If you have no control over that, then my soltuion might still work but would need to be optimized some more.

If it is a one-time cleanup , I think you need to create a table for temporary use which contains a list of all the character entities you might conceivably have in your db. You can google HTML character entities and find lists that with cutting and pasting will probably enable you to generate this table in less than 30 minutes.

Once you have this table, then your path is fairly clear. Like others said, you probably don't want to store encoded data in the DB like this. In which case, you just write a cursor and loop through your new table of character entities doing updates with replace statements. If you really want to replace only the ampersands though, then instead you loop through the cursor and replace the ampersand at the beginning of every character entity with some combination of characters that could never exist in the column otherwise like |#|#|# for instance. After that, the only ampersands left in the DB should be actual ampersands, not part of character entities. So then it's the same last two steps as I mentioned in my first paragraph: you run a single query to update all the ampersands to &. Then run another update statement replacing your special combination of characters with actual ampersands.

Answer 3

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