Go Premium for a chance to win a PS4. Enter to Win


SQL to update characters in table column

Posted on 2016-11-27
Medium Priority
Last Modified: 2016-11-27
I am using SQL 2008R2. I have a table named 'AdminChecklist', The column 'Docname'  is an nvarchar column.
It has values that may include characters such as '  or "  or &  or \  I need to go over every row of that column and replace those characters as listed below:

Replace:  &   FOR  &
Replace: ' FOR    '
Replace:  \  for "
Replace:   "   FOR  '

I am not 100% sure those are the unicode values I should use to replace especially  '  and  "  in the string. This way they should be displayed properly in the ASP page, currently the code gets broken by ' and " 

Help is appreciated.
Question by:Aleks
LVL 84

Expert Comment

by:David Johnson, CD, MVP
ID: 41903289
not uni-code but html representations for those characters.

Author Comment

ID: 41903291
That's ok what would the SQL be ?
LVL 30

Expert Comment

by:Alexandre Simões
ID: 41903387
Hi mate,

first of all, you shouldn't be doing this at the DB level.
What you want to do is called HTML encoding and, as the name leaks out, it's HTML specific. What if in som time, you want to display that data elsewhere, like in a report?... it won't be pretty.

This said, it should be your web presentation layer to encode the chars before adding the text to the page.


Now, if for some reason you really need to handle this at the DB level, then you need to know all chars and respective HTML encoded representation.
I've found an article on the web that does exactly that, fully explaining the process.
Check it here

Just for the sake of keeping this post self-contained, I'm copy/pasting the original code here, but I take no credit for it.

*** Please read the original post and read the code before running it ***

-- downloaded from http://wp.me/p5q5XS-4o																

/* Author: Manfred Kipfelsberger, Date: 2014-12-15, License: CC-BY																*/
/* Inspiration: https://devio.wordpress.com/2009/07/11/convert-unicode-hex-codepoint-to-unicode-character-in-sql-server/		*/
/* T-SQL UNICODE-Function: http://msdn.microsoft.com/en-us/library/ms180059.aspx												*/
/* The Role of Collations in String Conversion (CASE Sensitivity) : http://msdn.microsoft.com/en-us/library/ms190920.aspx		*/ 
/* SQL Server Collations and Unicode Support: http://msdn.microsoft.com/en-us/library/ms143726.aspx								*/ 
/* CASE Sensitivity in String Conversion: http://msdn.microsoft.com/en-us/library/ms190920.aspx									*/ 
/* HexStrToVarBinary taken from http://michaeldotnet.blogspot.de/2007/11/t-sql-hex-string-to-varbinary-improved.html			*/

-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
-- Helper function for hex conversion
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
IF OBJECT_ID(N'dbo.HexStrToVarBinary', 'FN') IS NOT NULL
	DROP FUNCTION dbo.HexStrToVarBinary

-- HexStrToVarBinary taken from http://michaeldotnet.blogspot.de/2007/11/t-sql-hex-string-to-varbinary-improved.html
CREATE FUNCTION dbo.HexStrToVarBinary(@hexstr varchar(8000))
RETURNS varbinary(8000)
    DECLARE @hex char(1), @i int, @place bigint, @a bigint
    SET @i = LEN(@hexstr) 

    set @place = convert(bigint,1)
    SET @a = convert(bigint, 0)

    WHILE (@i > 0 AND (substring(@hexstr, @i, 1) like '[0-9A-Fa-f]')) 
        SET @hex = SUBSTRING(@hexstr, @i, 1) 
        SET @a = @a + convert(bigint, 
			CASE WHEN @hex LIKE '[0-9]' 
			THEN CAST(@hex as int) 
			ELSE CAST(ASCII(UPPER(@hex))-55 as int) 
			END * @place)
		SET @place = @place * convert(bigint,16)
        SET @i = @i - 1

    RETURN convert(varbinary(8000),@a)

-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
-- 1. Stored Procedure p_CreateHtmlEntityTable
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

IF OBJECT_ID(N'p_CreateHtmlEntityTable', 'P') IS NOT NULL
   DROP PROCEDURE p_CreateHtmlEntityTable
CREATE PROCEDURE p_CreateHtmlEntityTable

	IF OBJECT_ID(N'HtmlEntities', 'U') IS NOT NULL DROP TABLE HtmlEntities;
	CREATE TABLE htmlEntities (
		charValue NCHAR, 
		htmlEntity NVARCHAR(20), 
		CodePointHex NVARCHAR(12), 
		UnicodeHex NVARCHAR(12), 
		UnicodeDec INT, 
		descr NVARCHAR(200)

	INSERT htmlEntities (htmlEntity, UnicodeHex, descr) VALUES
		('&amp;',	 '&#0026;',	 'ampersand'),-- erroneous conversion when after &quot;
		('&quot;',	 '&#0022;',	 'quotation mark (APL quote)'),
		('&apos;',	 '&#0027;',	 'apostrophe (apostrophe-quote); see below'),
		('&lt;',	 '&#003C;',	 'less-than sign'),
		('&gt;',	 '&#003E;',	 'greater-than sign'),
		('&iexcl;',	 '&#00A1;',	 'inverted exclamation mark'),
		('&cent;',	 '&#00A2;',	 'cent sign'),
		('&pound;',	 '&#00A3;',	 'pound sign'),
		('&curren;',	 '&#00A4;',	 'currency sign'),
		('&yen;',	 '&#00A5;',	 'yen sign (yuan sign)'),
		('&brvbar;',	 '&#00A6;',	 'broken bar (broken vertical bar)'),
		('&sect;',	 '&#00A7;',	 'section sign'),
		('&uml;',	 '&#00A8;',	 'diaeresis (spacing diaeresis); see Germanic umlaut'),
		('&copy;',	 '&#00A9;',	 'copyright symbol'),
		('&ordf;',	 '&#00AA;',	 'feminine ordinal indicator'),
		('&laquo;',	 '&#00AB;',	 'left-pointing double angle quotation mark (left pointing guillemet)'),
		('&not;',	 '&#00AC;',	 'not sign'),
		('&shy;',	 '&#00AD;',	 'soft hyphen (discretionary hyphen)'),
		('&reg;',	 '&#00AE;',	 'registered sign (registered trademark symbol)'),
		('&macr;',	 '&#00AF;',	 'macron (spacing macron, overline, APL overbar)'),
		('&deg;',	 '&#00B0;',	 'degree symbol'),
		('&plusmn;',	 '&#00B1;',	 'plus-minus sign (plus-or-minus sign)'),
		('&sup2;',	 '&#00B2;',	 'superscript two (superscript digit two, squared)'),
		('&sup3;',	 '&#00B3;',	 'superscript three (superscript digit three, cubed)'),
		('&acute;',	 '&#00B4;',	 'acute accent (spacing acute)'),
		('&micro;',	 '&#00B5;',	 'micro sign'),
		('&para;',	 '&#00B6;',	 'pilcrow sign (paragraph sign)'),
		('&middot;',	 '&#00B7;',	 'middle dot (Georgian comma, Greek middle dot)'),
		('&cedil;',	 '&#00B8;',	 'cedilla (spacing cedilla)'),
		('&sup1;',	 '&#00B9;',	 'superscript one (superscript digit one)'),
		('&ordm;',	 '&#00BA;',	 'masculine ordinal indicator'),
		('&raquo;',	 '&#00BB;',	 'right-pointing double angle quotation mark (right pointing guillemet)'),
		('&frac14;',	 '&#00BC;',	 'vulgar fraction one quarter (fraction one quarter)'),
		('&frac12;',	 '&#00BD;',	 'vulgar fraction one half (fraction one half)'),
		('&frac34;',	 '&#00BE;',	 'vulgar fraction three quarters (fraction three quarters)'),
		('&iquest;',	 '&#00BF;',	 'inverted question mark (turned question mark)'),
		('&Agrave;',	 '&#00C0;',	 'Latin capital letter A with grave accent (Latin capital letter A grave)'),
		('&Aacute;',	 '&#00C1;',	 'Latin capital letter A with acute accent'),
		('&Acirc;',	 '&#00C2;',	 'Latin capital letter A with circumflex'),
		('&Atilde;',	 '&#00C3;',	 'Latin capital letter A with tilde'),
		('&Auml;',	 '&#00C4;',	 'Latin capital letter A with diaeresis'),
		('&Aring;',	 '&#00C5;',	 'Latin capital letter A with ring above (Latin capital letter A ring)'),
		('&AElig;',	 '&#00C6;',	 'Latin capital letter AE (Latin capital ligature AE)'),
		('&Ccedil;',	 '&#00C7;',	 'Latin capital letter C with cedilla'),
		('&Egrave;',	 '&#00C8;',	 'Latin capital letter E with grave accent'),
		('&Eacute;',	 '&#00C9;',	 'Latin capital letter E with acute accent'),
		('&Ecirc;',	 '&#00CA;',	 'Latin capital letter E with circumflex'),
		('&Euml;',	 '&#00CB;',	 'Latin capital letter E with diaeresis'),
		('&Igrave;',	 '&#00CC;',	 'Latin capital letter I with grave accent'),
		('&Iacute;',	 '&#00CD;',	 'Latin capital letter I with acute accent'),
		('&Icirc;',	 '&#00CE;',	 'Latin capital letter I with circumflex'),
		('&Iuml;',	 '&#00CF;',	 'Latin capital letter I with diaeresis'),
		('&THORN;',	 '&#00DE;',	 'Latin capital letter THORN'),	-- erroneous conversion when after &ETH;
		('&ETH;',	 '&#00D0;',	 'Latin capital letter Eth'),
		('&Ntilde;',	 '&#00D1;',	 'Latin capital letter N with tilde'),
		('&Ograve;',	 '&#00D2;',	 'Latin capital letter O with grave accent'),
		('&Oacute;',	 '&#00D3;',	 'Latin capital letter O with acute accent'),
		('&Ocirc;',	 '&#00D4;',	 'Latin capital letter O with circumflex'),
		('&Otilde;',	 '&#00D5;',	 'Latin capital letter O with tilde'),
		('&Ouml;',	 '&#00D6;',	 'Latin capital letter O with diaeresis'),
		('&times;',	 '&#00D7;',	 'multiplication sign'),
		('&Oslash;',	 '&#00D8;',	 'Latin capital letter O with stroke (Latin capital letter O slash)'),
		('&Ugrave;',	 '&#00D9;',	 'Latin capital letter U with grave accent'),
		('&Uacute;',	 '&#00DA;',	 'Latin capital letter U with acute accent'),
		('&Ucirc;',	 '&#00DB;',	 'Latin capital letter U with circumflex'),
		('&Uuml;',	 '&#00DC;',	 'Latin capital letter U with diaeresis'),
		('&Yacute;',	 '&#00DD;',	 'Latin capital letter Y with acute accent'),
		('&szlig;',	 '&#00DF;',	 'Latin small letter sharp s (ess-zed); see German Eszett'),
		('&agrave;',	 '&#00E0;',	 'Latin small letter a with grave accent'),
		('&aacute;',	 '&#00E1;',	 'Latin small letter a with acute accent'),
		('&acirc;',	 '&#00E2;',	 'Latin small letter a with circumflex'),
		('&atilde;',	 '&#00E3;',	 'Latin small letter a with tilde'),
		('&auml;',	 '&#00E4;',	 'Latin small letter a with diaeresis'),
		('&aring;',	 '&#00E5;',	 'Latin small letter a with ring above'),
		('&aelig;',	 '&#00E6;',	 'Latin small letter ae (Latin small ligature ae)'),
		('&ccedil;',	 '&#00E7;',	 'Latin small letter c with cedilla'),
		('&egrave;',	 '&#00E8;',	 'Latin small letter e with grave accent'),
		('&eacute;',	 '&#00E9;',	 'Latin small letter e with acute accent'),
		('&ecirc;',	 '&#00EA;',	 'Latin small letter e with circumflex'),
		('&euml;',	 '&#00EB;',	 'Latin small letter e with diaeresis'),
		('&igrave;',	 '&#00EC;',	 'Latin small letter i with grave accent'),
		('&iacute;',	 '&#00ED;',	 'Latin small letter i with acute accent'),
		('&icirc;',	 '&#00EE;',	 'Latin small letter i with circumflex'),
		('&iuml;',	 '&#00EF;',	 'Latin small letter i with diaeresis'),
		('&thorn;',	 '&#00FE;',	 'Latin small letter thorn'),	-- erroneous conversion when after &eth;
		('&eth;',	 '&#00F0;',	 'Latin small letter eth'),
		('&ntilde;',	 '&#00F1;',	 'Latin small letter n with tilde'),
		('&ograve;',	 '&#00F2;',	 'Latin small letter o with grave accent'),
		('&oacute;',	 '&#00F3;',	 'Latin small letter o with acute accent'),
		('&ocirc;',	 '&#00F4;',	 'Latin small letter o with circumflex'),
		('&otilde;',	 '&#00F5;',	 'Latin small letter o with tilde'),
		('&ouml;',	 '&#00F6;',	 'Latin small letter o with diaeresis'),
		('&divide;',	 '&#00F7;',	 'division sign (obelus)'),
		('&oslash;',	 '&#00F8;',	 'Latin small letter o with stroke (Latin small letter o slash)'),
		('&ugrave;',	 '&#00F9;',	 'Latin small letter u with grave accent'),
		('&uacute;',	 '&#00FA;',	 'Latin small letter u with acute accent'),
		('&ucirc;',	 '&#00FB;',	 'Latin small letter u with circumflex'),
		('&uuml;',	 '&#00FC;',	 'Latin small letter u with diaeresis'),
		('&yacute;',	 '&#00FD;',	 'Latin small letter y with acute accent'),
		('&yuml;',	 '&#00FF;',	 'Latin small letter y with diaeresis'),
		('&OElig;',	 '&#0152;',	 'Latin capital ligature oe[e]'),
		('&oelig;',	 '&#0153;',	 'Latin small ligature oe[e]'),
		('&Scaron;',	 '&#0160;',	 'Latin capital letter s with caron'),
		('&scaron;',	 '&#0161;',	 'Latin small letter s with caron'),
		('&Yuml;',	 '&#0178;',	 'Latin capital letter y with diaeresis'),
		('&fnof;',	 '&#0192;',	 'Latin small letter f with hook (function, florin)'),
		('&circ;',	 '&#02C6;',	 'modifier letter circumflex accent'),
		('&tilde;',	 '&#02DC;',	 'small tilde'),
		('&Alpha;',	 '&#0391;',	 'Greek capital letter Alpha'),
		('&Beta;',	 '&#0392;',	 'Greek capital letter Beta'),
		('&Gamma;',	 '&#0393;',	 'Greek capital letter Gamma'),
		('&Delta;',	 '&#0394;',	 'Greek capital letter Delta'),
		('&Epsilon;',	 '&#0395;',	 'Greek capital letter Epsilon'),
		('&Zeta;',	 '&#0396;',	 'Greek capital letter Zeta'),
		('&Eta;',	 '&#0397;',	 'Greek capital letter Eta'),
		('&Theta;',	 '&#0398;',	 'Greek capital letter Theta'),
		('&Iota;',	 '&#0399;',	 'Greek capital letter Iota'),
		('&Kappa;',	 '&#039A;',	 'Greek capital letter Kappa'),
		('&Lambda;',	 '&#039B;',	 'Greek capital letter Lambda'),
		('&Mu;',	 '&#039C;',	 'Greek capital letter Mu'),
		('&Nu;',	 '&#039D;',	 'Greek capital letter Nu'),
		('&Xi;',	 '&#039E;',	 'Greek capital letter Xi'),
		('&Omicron;',	 '&#039F;',	 'Greek capital letter Omicron'),
		('&Pi;',	 '&#03A0;',	 'Greek capital letter Pi'),
		('&Rho;',	 '&#03A1;',	 'Greek capital letter Rho'),
		('&Sigma;',	 '&#03A3;',	 'Greek capital letter Sigma'),
		('&Tau;',	 '&#03A4;',	 'Greek capital letter Tau'),
		('&Upsilon;',	 '&#03A5;',	 'Greek capital letter Upsilon'),
		('&Phi;',	 '&#03A6;',	 'Greek capital letter Phi'),
		('&Chi;',	 '&#03A7;',	 'Greek capital letter Chi'),
		('&Psi;',	 '&#03A8;',	 'Greek capital letter Psi'),
		('&Omega;',	 '&#03A9;',	 'Greek capital letter Omega'),
		('&alpha;',	 '&#03B1;',	 'Greek small letter alpha'),
		('&beta;',	 '&#03B2;',	 'Greek small letter beta'),
		('&gamma;',	 '&#03B3;',	 'Greek small letter gamma'),
		('&delta;',	 '&#03B4;',	 'Greek small letter delta'),
		('&epsilon;',	 '&#03B5;',	 'Greek small letter epsilon'),
		('&zeta;',	 '&#03B6;',	 'Greek small letter zeta'),
		('&eta;',	 '&#03B7;',	 'Greek small letter eta'),
		('&theta;',	 '&#03B8;',	 'Greek small letter theta'),
		('&iota;',	 '&#03B9;',	 'Greek small letter iota'),
		('&kappa;',	 '&#03BA;',	 'Greek small letter kappa'),
		('&lambda;',	 '&#03BB;',	 'Greek small letter lambda'),
		('&mu;',	 '&#03BC;',	 'Greek small letter mu'),
		('&nu;',	 '&#03BD;',	 'Greek small letter nu'),
		('&xi;',	 '&#03BE;',	 'Greek small letter xi'),
		('&omicron;',	 '&#03BF;',	 'Greek small letter omicron'),
		('&pi;',	 '&#03C0;',	 'Greek small letter pi'),
		('&rho;',	 '&#03C1;',	 'Greek small letter rho'),
		('&sigmaf;',	 '&#03C2;',	 'Greek small letter final sigma'),
		('&sigma;',	 '&#03C3;',	 'Greek small letter sigma'),
		('&tau;',	 '&#03C4;',	 'Greek small letter tau'),
		('&upsilon;',	 '&#03C5;',	 'Greek small letter upsilon'),
		('&phi;',	 '&#03C6;',	 'Greek small letter phi'),
		('&chi;',	 '&#03C7;',	 'Greek small letter chi'),
		('&psi;',	 '&#03C8;',	 'Greek small letter psi'),
		('&omega;',	 '&#03C9;',	 'Greek small letter omega'),
		('&thetasym;',	 '&#03D1;',	 'Greek theta symbol'),
		('&upsih;',	 '&#03D2;',	 'Greek Upsilon with hook symbol'),
		('&piv;',	 '&#03D6;',	 'Greek pi symbol'),
		('&ndash;',	 '&#2013;',	 'en dash'),
		('&mdash;',	 '&#2014;',	 'em dash'),
		('&lsquo;',	 '&#2018;',	 'left single quotation mark'),
		('&rsquo;',	 '&#2019;',	 'right single quotation mark'),
		('&sbquo;',	 '&#201A;',	 'single low-9 quotation mark'),
		('&ldquo;',	 '&#201C;',	 'left double quotation mark'),
		('&rdquo;',	 '&#201D;',	 'right double quotation mark'),
		('&bdquo;',	 '&#201E;',	 'double low-9 quotation mark'),
		('&dagger;',	 '&#2020;',	 'dagger, obelisk'),
		('&Dagger;',	 '&#2021;',	 'double dagger, double obelisk'),
		('&bull;',	 '&#2022;',	 'bullet (black small circle)[f]'),
		('&hellip;',	 '&#2026;',	 'horizontal ellipsis (three dot leader)'),
		('&permil;',	 '&#2030;',	 'per mille sign'),
		('&prime;',	 '&#2032;',	 'prime (minutes, feet)'),
		('&Prime;',	 '&#2033;',	 'double prime (seconds, inches)'),
		('&lsaquo;',	 '&#2039;',	 'single left-pointing angle quotation mark[g]'),
		('&rsaquo;',	 '&#203A;',	 'single right-pointing angle quotation mark[g]'),
		('&oline;',	 '&#203E;',	 'overline (spacing overscore)'),
		('&frasl;',	 '&#2044;',	 'fraction slash (solidus)'),
		('&euro;',	 '&#20AC;',	 'euro sign'),
		('&image;',	 '&#2111;',	 'black-letter capital I (imaginary part)'),
		('&weierp;',	 '&#2118;',	 'script capital P (power set, Weierstrass p)'),
		('&real;',	 '&#211C;',	 'black-letter capital R (real part symbol)'),
		('&trade;',	 '&#2122;',	 'trademark symbol'),
		('&alefsym;',	 '&#2135;',	 'alef symbol (first transfinite cardinal)[h]'),
		('&larr;',	 '&#2190;',	 'leftwards arrow'),
		('&uarr;',	 '&#2191;',	 'upwards arrow'),
		('&rarr;',	 '&#2192;',	 'rightwards arrow'),
		('&darr;',	 '&#2193;',	 'downwards arrow'),
		('&harr;',	 '&#2194;',	 'left right arrow'),
		('&crarr;',	 '&#21B5;',	 'downwards arrow with corner leftwards (carriage return)'),
		('&lArr;',	 '&#21D0;',	 'leftwards double arrow[i]'),
		('&uArr;',	 '&#21D1;',	 'upwards double arrow'),
		('&rArr;',	 '&#21D2;',	 'rightwards double arrow[j]'),
		('&dArr;',	 '&#21D3;',	 'downwards double arrow'),
		('&hArr;',	 '&#21D4;',	 'left right double arrow'),
		('&forall;',	 '&#2200;',	 'for all'),
		('&part;',	 '&#2202;',	 'partial differential'),
		('&exist;',	 '&#2203;',	 'there exists'),
		('&empty;',	 '&#2205;',	 'empty set (null set); see also U+8960, ?'),
		('&nabla;',	 '&#2207;',	 'del or nabla (vector differential operator)'),
		('&isin;',	 '&#2208;',	 'element of'),
		('&notin;',	 '&#2209;',	 'not an element of'),
		('&ni;',	 '&#220B;',	 'contains as member'),
		('&prod;',	 '&#220F;',	 'n-ary product (product sign)[k]'),
		('&sum;',	 '&#2211;',	 'n-ary summation[l]'),
		('&minus;',	 '&#2212;',	 'minus sign'),
		('&lowast;',	 '&#2217;',	 'asterisk operator'),
		('&radic;',	 '&#221A;',	 'square root (radical sign)'),
		('&prop;',	 '&#221D;',	 'proportional to'),
		('&infin;',	 '&#221E;',	 'infinity'),
		('&ang;',	 '&#2220;',	 'angle'),
		('&and;',	 '&#2227;',	 'logical and (wedge)'),
		('&or;',	 '&#2228;',	 'logical or (vee)'),
		('&cap;',	 '&#2229;',	 'intersection (cap)'),
		('&cup;',	 '&#222A;',	 'union (cup)'),
		('&int;',	 '&#222B;',	 'integral'),
		('&there4;',	 '&#2234;',	 'therefore sign'),
		('&sim;',	 '&#223C;',	 'tilde operator (varies with, similar to)[m]'),
		('&cong;',	 '&#2245;',	 'congruent to'),
		('&asymp;',	 '&#2248;',	 'almost equal to (asymptotic to)'),
		('&ne;',	 '&#2260;',	 'not equal to'),
		('&equiv;',	 '&#2261;',	 'identical to; sometimes used for equivalent to'),
		('&le;',	 '&#2264;',	 'less-than or equal to'),
		('&ge;',	 '&#2265;',	 'greater-than or equal to'),
		('&sub;',	 '&#2282;',	 'subset of'),
		('&sup;',	 '&#2283;',	 'superset of[n]'),
		('&nsub;',	 '&#2284;',	 'not a subset of'),
		('&sube;',	 '&#2286;',	 'subset of or equal to'),
		('&supe;',	 '&#2287;',	 'superset of or equal to'),
		('&oplus;',	 '&#2295;',	 'circled plus (direct sum)'),
		('&otimes;',	 '&#2297;',	 'circled times (vector product)'),
		('&perp;',	 '&#22A5;',	 'up tack (orthogonal to, perpendicular)[o]'),
		('&sdot;',	 '&#22C5;',	 'dot operator[p]'),
		('&vellip;',	 '&#22EE;',	 'vertical ellipsis'),
		('&lceil;',	 '&#2308;',	 'left ceiling (APL upstile)'),
		('&rceil;',	 '&#2309;',	 'right ceiling'),
		('&lfloor;',	 '&#230A;',	 'left floor (APL downstile)'),
		('&rfloor;',	 '&#230B;',	 'right floor'),
		('&lang;',	 '&#2329;',	 'left-pointing angle bracket (bra)[q]'),
		('&rang;',	 '&#232A;',	 'right-pointing angle bracket (ket)[r]'),
		('&loz;',	 '&#25CA;',	 'lozenge'),
		('&spades;',	 '&#2660;',	 'black spade suit[f]'),
		('&clubs;',	 '&#2663;',	 'black club suit (shamrock)[f]'),
		('&hearts;',	 '&#2665;',	 'black heart suit (valentine)[f]'),
		('&diams;',	 '&#2666;',	 'black diamond suit[f]');

	UPDATE htmlEntities
	SET CodePointHex = Replace(Replace(UnicodeHex, '&#', ''), ';', '')
	FROM htmlEntities;
	UPDATE htmlEntities
	SET UnicodeDec = CONVERT(INT, dbo.HexStrToVarBinary(CodePointHex)),
		charValue = NCHAR(CONVERT(INT, dbo.HexStrToVarBinary(CodePointHex)))
	FROM htmlEntities


-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
-- 2. Function f_htmlEncode
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 


IF OBJECT_ID('f_htmlEncode', 'FN') IS NOT NULL 
	DROP FUNCTION f_htmlEncode
	-- Declare the return variable here
	DECLARE @result NVARCHAR(MAX) = @input;
	SELECT @result = REPLACE(@result COLLATE Latin1_General_CS_AS, NCHAR(UnicodeDec), htmlEntity) 
	FROM htmlEntities OPTION (MAXRECURSION 32000)
	RETURN @result


-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
-- 3. Usage and Test
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

EXEC p_CreateHtmlEntityTable;

PRINT 'Testing html encoding for all characters in table htmlEntities'

DECLARE @specialChars NVARCHAR(MAX) = ''
SELECT @specialChars = COALESCE(@specialChars + '|' + NCHAR(UnicodeDec), '') FROM htmlEntities
PRINT @specialChars

DECLARE @htmlEntities NVARCHAR(MAX) = ''
SELECT @htmlEntities = COALESCE(@htmlEntities + '|' + htmlEntity, '') FROM htmlEntities

DECLARE @convHtmlEntities NVARCHAR(MAX) = ''
SELECT @convHtmlEntities = dbo.f_htmlEncode(@specialChars)

IF @convHtmlEntities = @htmlEntities PRINT 'success' 
	PRINT 'Encoding failed!'
	PRINT '@Conversion result ='
	PRINT @convHtmlEntities
	PRINT ' '
	PRINT 'Expected result = '
	PRINT @HtmlEntities

Open in new window

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.


Author Comment

ID: 41903411
The app was patched to encode the characters afterwards. The asp pages will decode where needed.

I just need an SQL that will replace a given character in the nvarchar field with other characters.
LVL 30

Assisted Solution

by:Alexandre Simões
Alexandre Simões earned 1000 total points
ID: 41903453
so if you want to do it by hand, I've created this function for you to use:
	DECLARE @result AS NVARCHAR(MAX) = @input;

	SET @result = REPLACE(@result, '&', '&amp;');
	SET @result = REPLACE(@result, '<', '&lt;');
	SET @result = REPLACE(@result, '>', '&gt;');
	-- and so on

	RETURN @result;

Open in new window

To use it, simply call it like this:
UPDATE MyTable SET Column1 = dbo.HTMLEncode(MyColumnToEncode);

Open in new window

Currently it only supports &, < and > chars. Add as many as you need.
For obvious reasons, &amp; has to be to first on the list of replacements.

LVL 35

Accepted Solution

Pawan Kumar earned 1000 total points
ID: 41903496
Try..  You can choose your where clause as per you need. You can use below if you dont want to create a new function.


UPDATE YourTable 
SET YourColumnName = REPLACE(REPLACE(REPLACE(YourColumnName , '&', '&amp;'), '<', '&lt;'), '>', '&gt;')


Open in new window


Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question