SQL to update characters in table column
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.
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.
David Johnson, CD
not uni-code but html representations for those characters.
ASKER
That's ok what would the SQL be ?
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 ***
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
('&', '', 'ampersand'),-- erroneous conversion when after "
('"', '', 'quotation mark (APL quote)'),
(''', '', 'apostrophe (apostrophe-quote); see below'),
('<', 'C;', 'less-than sign'),
('>', 'E;', 'greater-than sign'),
('¡', '�A1;', 'inverted exclamation mark'),
('¢', '�A2;', 'cent sign'),
('£', '�A3;', 'pound sign'),
('¤', '�A4;', 'currency sign'),
('¥', '�A5;', 'yen sign (yuan sign)'),
('¦', '�A6;', 'broken bar (broken vertical bar)'),
('§', '�A7;', 'section sign'),
('¨', '�A8;', 'diaeresis (spacing diaeresis); see Germanic umlaut'),
('©', '�A9;', 'copyright symbol'),
('ª', '�AA;', 'feminine ordinal indicator'),
('«', '�AB;', 'left-pointing double angle quotation mark (left pointing guillemet)'),
('¬', '�AC;', 'not sign'),
('­', '�AD;', 'soft hyphen (discretionary hyphen)'),
('®', '�AE;', 'registered sign (registered trademark symbol)'),
('¯', '�AF;', 'macron (spacing macron, overline, APL overbar)'),
('°', '�B0;', 'degree symbol'),
('±', '�B1;', 'plus-minus sign (plus-or-minus sign)'),
('²', '�B2;', 'superscript two (superscript digit two, squared)'),
('³', '�B3;', 'superscript three (superscript digit three, cubed)'),
('´', '�B4;', 'acute accent (spacing acute)'),
('µ', '�B5;', 'micro sign'),
('¶', '�B6;', 'pilcrow sign (paragraph sign)'),
('·', '�B7;', 'middle dot (Georgian comma, Greek middle dot)'),
('¸', '�B8;', 'cedilla (spacing cedilla)'),
('¹', '�B9;', 'superscript one (superscript digit one)'),
('º', '�BA;', 'masculine ordinal indicator'),
('»', '�BB;', 'right-pointing double angle quotation mark (right pointing guillemet)'),
('¼', '�BC;', 'vulgar fraction one quarter (fraction one quarter)'),
('½', '�BD;', 'vulgar fraction one half (fraction one half)'),
('¾', '�BE;', 'vulgar fraction three quarters (fraction three quarters)'),
('¿', '�BF;', 'inverted question mark (turned question mark)'),
('À', '�C0;', 'Latin capital letter A with grave accent (Latin capital letter A grave)'),
('Á', '�C1;', 'Latin capital letter A with acute accent'),
('Â', '�C2;', 'Latin capital letter A with circumflex'),
('Ã', '�C3;', 'Latin capital letter A with tilde'),
('Ä', '�C4;', 'Latin capital letter A with diaeresis'),
('Å', '�C5;', 'Latin capital letter A with ring above (Latin capital letter A ring)'),
('Æ', '�C6;', 'Latin capital letter AE (Latin capital ligature AE)'),
('Ç', '�C7;', 'Latin capital letter C with cedilla'),
('È', '�C8;', 'Latin capital letter E with grave accent'),
('É', '�C9;', 'Latin capital letter E with acute accent'),
('Ê', '�CA;', 'Latin capital letter E with circumflex'),
('Ë', '�CB;', 'Latin capital letter E with diaeresis'),
('Ì', '�CC;', 'Latin capital letter I with grave accent'),
('Í', '�CD;', 'Latin capital letter I with acute accent'),
('Î', '�CE;', 'Latin capital letter I with circumflex'),
('Ï', '�CF;', 'Latin capital letter I with diaeresis'),
('Þ', '�DE;', 'Latin capital letter THORN'), -- erroneous conversion when after Ð
('Ð', '�D0;', 'Latin capital letter Eth'),
('Ñ', '�D1;', 'Latin capital letter N with tilde'),
('Ò', '�D2;', 'Latin capital letter O with grave accent'),
('Ó', '�D3;', 'Latin capital letter O with acute accent'),
('Ô', '�D4;', 'Latin capital letter O with circumflex'),
('Õ', '�D5;', 'Latin capital letter O with tilde'),
('Ö', '�D6;', 'Latin capital letter O with diaeresis'),
('×', '�D7;', 'multiplication sign'),
('Ø', '�D8;', 'Latin capital letter O with stroke (Latin capital letter O slash)'),
('Ù', '�D9;', 'Latin capital letter U with grave accent'),
('Ú', '�DA;', 'Latin capital letter U with acute accent'),
('Û', '�DB;', 'Latin capital letter U with circumflex'),
('Ü', '�DC;', 'Latin capital letter U with diaeresis'),
('Ý', '�DD;', 'Latin capital letter Y with acute accent'),
('ß', '�DF;', 'Latin small letter sharp s (ess-zed); see German Eszett'),
('à', '�E0;', 'Latin small letter a with grave accent'),
('á', '�E1;', 'Latin small letter a with acute accent'),
('â', '�E2;', 'Latin small letter a with circumflex'),
('ã', '�E3;', 'Latin small letter a with tilde'),
('ä', '�E4;', 'Latin small letter a with diaeresis'),
('å', '�E5;', 'Latin small letter a with ring above'),
('æ', '�E6;', 'Latin small letter ae (Latin small ligature ae)'),
('ç', '�E7;', 'Latin small letter c with cedilla'),
('è', '�E8;', 'Latin small letter e with grave accent'),
('é', '�E9;', 'Latin small letter e with acute accent'),
('ê', '�EA;', 'Latin small letter e with circumflex'),
('ë', '�EB;', 'Latin small letter e with diaeresis'),
('ì', '�EC;', 'Latin small letter i with grave accent'),
('í', '�ED;', 'Latin small letter i with acute accent'),
('î', '�EE;', 'Latin small letter i with circumflex'),
('ï', '�EF;', 'Latin small letter i with diaeresis'),
('þ', '�FE;', 'Latin small letter thorn'), -- erroneous conversion when after ð
('ð', '�F0;', 'Latin small letter eth'),
('ñ', '�F1;', 'Latin small letter n with tilde'),
('ò', '�F2;', 'Latin small letter o with grave accent'),
('ó', '�F3;', 'Latin small letter o with acute accent'),
('ô', '�F4;', 'Latin small letter o with circumflex'),
('õ', '�F5;', 'Latin small letter o with tilde'),
('ö', '�F6;', 'Latin small letter o with diaeresis'),
('÷', '�F7;', 'division sign (obelus)'),
('ø', '�F8;', 'Latin small letter o with stroke (Latin small letter o slash)'),
('ù', '�F9;', 'Latin small letter u with grave accent'),
('ú', '�FA;', 'Latin small letter u with acute accent'),
('û', '�FB;', 'Latin small letter u with circumflex'),
('ü', '�FC;', 'Latin small letter u with diaeresis'),
('ý', '�FD;', 'Latin small letter y with acute accent'),
('ÿ', '�FF;', 'Latin small letter y with diaeresis'),
('Œ', '˜', 'Latin capital ligature oe[e]'),
('œ', '™', 'Latin small ligature oe[e]'),
('Š', ' ', 'Latin capital letter s with caron'),
('š', '¡', 'Latin small letter s with caron'),
('Ÿ', '²', 'Latin capital letter y with diaeresis'),
('ƒ', 'À', 'Latin small letter f with hook (function, florin)'),
('ˆ', 'C6;', 'modifier letter circumflex accent'),
('˜', 'DC;', 'small tilde'),
('Α', 'Ƈ', 'Greek capital letter Alpha'),
('Β', 'ƈ', 'Greek capital letter Beta'),
('Γ', 'Ɖ', 'Greek capital letter Gamma'),
('Δ', 'Ɗ', 'Greek capital letter Delta'),
('Ε', 'Ƌ', 'Greek capital letter Epsilon'),
('Ζ', 'ƌ', 'Greek capital letter Zeta'),
('Η', 'ƍ', 'Greek capital letter Eta'),
('Θ', 'Ǝ', 'Greek capital letter Theta'),
('Ι', 'Ə', 'Greek capital letter Iota'),
('Κ', ''A;', 'Greek capital letter Kappa'),
('Λ', ''B;', 'Greek capital letter Lambda'),
('Μ', ''C;', 'Greek capital letter Mu'),
('Ν', ''D;', 'Greek capital letter Nu'),
('Ξ', ''E;', 'Greek capital letter Xi'),
('Ο', ''F;', 'Greek capital letter Omicron'),
('Π', 'A0;', 'Greek capital letter Pi'),
('Ρ', 'A1;', 'Greek capital letter Rho'),
('Σ', 'A3;', 'Greek capital letter Sigma'),
('Τ', 'A4;', 'Greek capital letter Tau'),
('Υ', 'A5;', 'Greek capital letter Upsilon'),
('Φ', 'A6;', 'Greek capital letter Phi'),
('Χ', 'A7;', 'Greek capital letter Chi'),
('Ψ', 'A8;', 'Greek capital letter Psi'),
('Ω', 'A9;', 'Greek capital letter Omega'),
('α', 'B1;', 'Greek small letter alpha'),
('β', 'B2;', 'Greek small letter beta'),
('γ', 'B3;', 'Greek small letter gamma'),
('δ', 'B4;', 'Greek small letter delta'),
('ε', 'B5;', 'Greek small letter epsilon'),
('ζ', 'B6;', 'Greek small letter zeta'),
('η', 'B7;', 'Greek small letter eta'),
('θ', 'B8;', 'Greek small letter theta'),
('ι', 'B9;', 'Greek small letter iota'),
('κ', 'BA;', 'Greek small letter kappa'),
('λ', 'BB;', 'Greek small letter lambda'),
('μ', 'BC;', 'Greek small letter mu'),
('ν', 'BD;', 'Greek small letter nu'),
('ξ', 'BE;', 'Greek small letter xi'),
('ο', 'BF;', 'Greek small letter omicron'),
('π', 'C0;', 'Greek small letter pi'),
('ρ', 'C1;', 'Greek small letter rho'),
('ς', 'C2;', 'Greek small letter final sigma'),
('σ', 'C3;', 'Greek small letter sigma'),
('τ', 'C4;', 'Greek small letter tau'),
('υ', 'C5;', 'Greek small letter upsilon'),
('φ', 'C6;', 'Greek small letter phi'),
('χ', 'C7;', 'Greek small letter chi'),
('ψ', 'C8;', 'Greek small letter psi'),
('ω', 'C9;', 'Greek small letter omega'),
('ϑ', 'D1;', 'Greek theta symbol'),
('ϒ', 'D2;', 'Greek Upsilon with hook symbol'),
('ϖ', 'D6;', 'Greek pi symbol'),
('–', 'ߝ', 'en dash'),
('—', 'ߞ', 'em dash'),
('‘', 'ߢ', 'left single quotation mark'),
('’', 'ߣ', 'right single quotation mark'),
('‚', 'ÉA;', 'single low-9 quotation mark'),
('“', 'ÉC;', 'left double quotation mark'),
('”', 'ÉD;', 'right double quotation mark'),
('„', 'ÉE;', 'double low-9 quotation mark'),
('†', 'ߤ', 'dagger, obelisk'),
('‡', 'ߥ', 'double dagger, double obelisk'),
('•', 'ߦ', 'bullet (black small circle)[f]'),
('…', 'ߪ', 'horizontal ellipsis (three dot leader)'),
('‰', '߮', 'per mille sign'),
('′', '߰', 'prime (minutes, feet)'),
('″', '߱', 'double prime (seconds, inches)'),
('‹', '߷', 'single left-pointing angle quotation mark[g]'),
('›', 'ËA;', 'single right-pointing angle quotation mark[g]'),
('‾', 'ËE;', 'overline (spacing overscore)'),
('⁄', '߼', 'fraction slash (solidus)'),
('€', 'AC;', 'euro sign'),
('ℑ', '࠿', 'black-letter capital I (imaginary part)'),
('℘', 'ࡆ', 'script capital P (power set, Weierstrass p)'),
('ℜ', 'ÓC;', 'black-letter capital R (real part symbol)'),
('™', 'ࡊ', 'trademark symbol'),
('ℵ', 'ࡗ', 'alef symbol (first transfinite cardinal)[h]'),
('←', 'ࢎ', 'leftwards arrow'),
('↑', '࢏', 'upwards arrow'),
('→', '࢐', 'rightwards arrow'),
('↓', '࢑', 'downwards arrow'),
('↔', '࢒', 'left right arrow'),
('↵', 'B5;', 'downwards arrow with corner leftwards (carriage return)'),
('⇐', 'D0;', 'leftwards double arrow[i]'),
('⇑', 'D1;', 'upwards double arrow'),
('⇒', 'D2;', 'rightwards double arrow[j]'),
('⇓', 'D3;', 'downwards double arrow'),
('⇔', 'D4;', 'left right double arrow'),
('∀', '࢘', 'for all'),
('∂', '࢚', 'partial differential'),
('∃', '࢛', 'there exists'),
('∅', '࢝', 'empty set (null set); see also U+8960, ?'),
('∇', '࢟', 'del or nabla (vector differential operator)'),
('∈', 'ࢠ', 'element of'),
('∉', 'ࢡ', 'not an element of'),
('∋', 'ÜB;', 'contains as member'),
('∏', 'ÜF;', 'n-ary product (product sign)[k]'),
('∑', 'ࢣ', 'n-ary summation[l]'),
('−', 'ࢤ', 'minus sign'),
('∗', 'ࢩ', 'asterisk operator'),
('√', 'ÝA;', 'square root (radical sign)'),
('∝', 'ÝD;', 'proportional to'),
('∞', 'ÝE;', 'infinity'),
('∠', 'ࢬ', 'angle'),
('∧', 'ࢳ', 'logical and (wedge)'),
('∨', 'ࢴ', 'logical or (vee)'),
('∩', 'ࢵ', 'intersection (cap)'),
('∪', 'ÞA;', 'union (cup)'),
('∫', 'ÞB;', 'integral'),
('∴', 'ࢺ', 'therefore sign'),
('∼', 'ßC;', 'tilde operator (varies with, similar to)[m]'),
('≅', 'ࣅ', 'congruent to'),
('≈', 'ࣈ', 'almost equal to (asymptotic to)'),
('≠', 'ࣔ', 'not equal to'),
('≡', 'ࣕ', 'identical to; sometimes used for equivalent to'),
('≤', 'ࣘ', 'less-than or equal to'),
('≥', 'ࣙ', 'greater-than or equal to'),
('⊂', '࣪', 'subset of'),
('⊃', '࣫', 'superset of[n]'),
('⊄', '࣬', 'not a subset of'),
('⊆', '࣮', 'subset of or equal to'),
('⊇', '࣯', 'superset of or equal to'),
('⊕', 'ࣷ', 'circled plus (direct sum)'),
('⊗', 'ࣹ', 'circled times (vector product)'),
('⊥', 'A5;', 'up tack (orthogonal to, perpendicular)[o]'),
('⋅', 'C5;', 'dot operator[p]'),
('⋮', 'EE;', 'vertical ellipsis'),
('⌈', 'ऄ', 'left ceiling (APL upstile)'),
('⌉', 'अ', 'right ceiling'),
('⌊', 'æA;', 'left floor (APL downstile)'),
('⌋', 'æB;', 'right floor'),
('⟨', 'ङ', 'left-pointing angle bracket (bra)[q]'),
('⟩', 'èA;', 'right-pointing angle bracket (ket)[r]'),
('◊', 'CA;', 'lozenge'),
('♠', '੤', 'black spade suit[f]'),
('♣', '੧', 'black club suit (shamrock)[f]'),
('♥', '੩', 'black heart suit (valentine)[f]'),
('♦', '੪', '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
ENDASKER
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.
I just need an SQL that will replace a given character in the nvarchar field with other characters.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.