0

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Totoy Maskulado
  • 39
  • 1
  • 7
  • Use replace(col, '&', '&') – radar Dec 09 '14 at 01:44
  • Which direction do you want to go? `&` -> `&`, or vice-versa? – Matthew Haugen Dec 09 '14 at 01:45
  • not sure it sound stupid or better way, you can reply all  -> & then only replace all the & to   – ah_hau Dec 09 '14 at 01:54
  • 2
    No easy way, sadly. It is easier to convert entities back to characters than vice versa. Custom function will help, of course, but I highly doubt the very reason to keep entities in DB. – Alex Skalozub Dec 09 '14 at 01:57
  • complete your query and in your server side code urldecode it - that will turn & to & and leave & alone. Then urlencode it and you'll get & for all. But the real answer is to sanitize your data when inserting/updating – Lea Dec 09 '14 at 02:20
  • gah not urlencode/decode. htmlentities and html_entity_decode (for php) I'm sure your language has something similar. – Lea Dec 09 '14 at 02:26
  • @Lea Thanks, I used it and it worked. But I need to Decode it first before re-Encoding it again. – Totoy Maskulado Dec 09 '14 at 03:14
  • @ah_hau Actually, that's what I first thought. Thanks anyway. – Totoy Maskulado Dec 09 '14 at 03:14
  • 1
    you should not alter the content of the database. Use raw content there and encode/decode it in one of other tier layer. – Sebas Dec 09 '14 at 03:17

3 Answers3

0
  1. If you really want to replace it in your database, you can try running
    UPDATE Table1 SET Column1 = REPLACE(Column1, '&', '&');
  1. 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.)

Hans Kesting
  • 38,117
  • 9
  • 79
  • 111
kyo
  • 645
  • 4
  • 7
  • Thank you for your answer. Actually, I already tried using the HttpUtility.Encode (I am actually using ASP.Net Webpage). It did change all '&' to '&' but it did not change other character entities (  , etc.), that's why I'm thinking of replacing them in the DB. – Totoy Maskulado Dec 09 '14 at 02:41
  • Thanks! I fixed my problem by Decoding and Re-Encoding it again. Thank you very much! – Totoy Maskulado Dec 09 '14 at 03:15
0

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.

EGP
  • 616
  • 5
  • 11
0
--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))
Nimantha
  • 6,405
  • 6
  • 28
  • 69
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219