Solved

SQL to update characters in table column

Posted on 2016-11-27
6
30 Views
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.
0
Comment
Question by:amucinobluedot
6 Comments
 
LVL 78

Expert Comment

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

Author Comment

by:amucinobluedot
ID: 41903291
That's ok what would the SQL be ?
0
 
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																

/********************************************************************************************************************************/
/* CONVERT SPECIAL CHARACTERS TO HTML ENTITIES																					*/
/********************************************************************************************************************************/
/* 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			*/
/********************************************************************************************************************************/
USE <YOUR_DATABASE_NAME_HERE>
GO

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

-- 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)
AS
BEGIN 
	
    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]')) 
     BEGIN 
        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
     END 

    RETURN convert(varbinary(8000),@a)
END
GO 


-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
-- 1. Stored Procedure p_CreateHtmlEntityTable
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
USE <YOUR_DATABASE_NAME_HERE>
GO

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

	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

END
GO



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

USE <YOUR_DATABASE_NAME_HERE>
GO

IF OBJECT_ID('f_htmlEncode', 'FN') IS NOT NULL 
	DROP FUNCTION f_htmlEncode
GO
CREATE FUNCTION f_htmlEncode (@input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS BEGIN
	-- 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

END
GO



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

EXEC p_CreateHtmlEntityTable;

PRINT ' '
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' 
ELSE BEGIN
	PRINT 'Encoding failed!'
	PRINT '@Conversion result ='
	PRINT @convHtmlEntities
	PRINT ' '
	PRINT 'Expected result = '
	PRINT @HtmlEntities
END

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:amucinobluedot
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.
0
 
LVL 30

Assisted Solution

by:Alexandre Simões
Alexandre Simões earned 250 total points
ID: 41903453
Ok,
so if you want to do it by hand, I've created this function for you to use:
CREATE FUNCTION HTMLEncode 
(
	@input AS NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	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;
END
GO

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.

Cheers!
0
 
LVL 18

Accepted Solution

by:
Pawan Kumar Khowal earned 250 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


--
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now