?
Solved

Replacing special characters for storage in SQL and then output to browser

Posted on 2011-03-22
8
Medium Priority
?
626 Views
Last Modified: 2012-05-11
I have an html form with which the user is creating a title and description of an item. This data is then stored in MS SQL and later retrieved for display in the browser.

The problem is that some users enter names with special characters, such as Spanish names (eg. Pérez or Migüel etc.). There are even instances of users entering open or closed quotes rather than the standard straight quotation marks (probably from cut & pasting from a Word Doc I would guess).

In any event all of these special characters are showing up in the DB exactly as they are entered, but when rendered in the browser they are being displayed as a funky question mark symbol (¿).

I do not want to spend the time writing a replace function to account for all the possible variations of special characters.

How best to handle this?  Does anyone have a ready-made classic ASP function or RegExp that I can use to convert the characters to html for storage or some other solution please?

Needs to be in Classic ASP.  Thanks.
0
Comment
Question by:worthyking1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 2

Expert Comment

by:rhawk
ID: 35194279
Are you storing them as a VARCHAR or NVARCHAR? That MAY be the issue.
0
 
LVL 11

Expert Comment

by:G_H
ID: 35194830
Try this function (NOT TESTED) but lurking in my library...

GH

 
Function CleanDodgyChars(str)
	' str = Replace(str, "<", "&lt;")
	' str = Replace(str, ">", "&gt;")
	' str = Replace(str, "&", "&amp;")
	' str = Replace(str, """", "&quot;")
	' str = Replace(str, " ", "&nbsp;")
	str = "" & str
	str = Replace(str, "©", "&copy;")
	str = Replace(str, "Ç", "&Ccedil;")
	str = Replace(str, "ç", "&ccdil;")
	str = Replace(str, "Ñ", "&Ntilde;")
	str = Replace(str, "ñ", "&ntilde;")
	str = Replace(str, "Þ", "&THORN;")
	str = Replace(str, "þ", "&thorn;")
	str = Replace(str, "Ý", "&Yacute;")
	str = Replace(str, "ý", "&yacute;")
	str = Replace(str, "ÿ", "&yuml;")
	str = Replace(str, "ß", "&szlig;")
	str = Replace(str, "Æ", "&AElig;")
	str = Replace(str, "Á", "&Aacute;")
	str = Replace(str, "Â", "&Acirc;")
	str = Replace(str, "À", "&Agrave;")
	str = Replace(str, "Å", "&Aring;")
	str = Replace(str, "Ã", "&Atilde;")
	str = Replace(str, "Ä", "&Auml;")
	str = Replace(str, "æ", "&aelig;")
	str = Replace(str, "á", "&aacute;")
	str = Replace(str, "â", "&acirc;")
	str = Replace(str, "à", "&agrave;")
	str = Replace(str, "å", "&aring;")
	str = Replace(str, "ã", "&atilde;")
	str = Replace(str, "ä", "&auml;")
	str = Replace(str, "Ð", "&ETH;")
	str = Replace(str, "É", "&Eacute;")
	str = Replace(str, "Ê", "&Ecirc;")
	str = Replace(str, "È", "&Egrave;")
	str = Replace(str, "Ë", "&Euml;")
	str = Replace(str, "ð", "&eth;")
	str = Replace(str, "é", "&eacute;")
	str = Replace(str, "ê", "&ecirc;")
	str = Replace(str, "è", "&egrave;")
	str = Replace(str, "ë", "&euml;")
	str = Replace(str, "Í", "&Iacute;")
	str = Replace(str, "Î", "&Icirc;")
	str = Replace(str, "Ì", "&Igrave;")
	str = Replace(str, "Ï", "&Iuml;")
	str = Replace(str, "í", "&iacute;")
	str = Replace(str, "î", "&icirc;")
	str = Replace(str, "ì", "&igrave;")
	str = Replace(str, "ï", "&iuml;")
	str = Replace(str, "Ó", "&Oacute;")
	str = Replace(str, "Ô", "&Ocirc;")
	str = Replace(str, "Ò", "&Ograve;")
	str = Replace(str, "Ø", "&Oslash;")
	str = Replace(str, "Õ", "&Otilde;")
	str = Replace(str, "Ö", "&Ouml;")
	str = Replace(str, "ó", "&oacute;")
	str = Replace(str, "ô", "&ocirc;")
	str = Replace(str, "ò", "&ograve;")
	str = Replace(str, "ø", "&oslash;")
	str = Replace(str, "õ", "&otilde;")
	str = Replace(str, "ö", "&ouml;")
	str = Replace(str, "Ú", "&Uacute;")
	str = Replace(str, "Û", "&Ucirc;")
	str = Replace(str, "Ù", "&Ugrave;")
	str = Replace(str, "Ü", "&Uuml;")
	str = Replace(str, "ú", "&uacute;")
	str = Replace(str, "û", "&ucirc;")
	str = Replace(str, "ù", "&ugrave;")
	str = Replace(str, "ü", "&uuml;")
	str = Replace(str, "®", "&reg;")
	str = Replace(str, "±", "&plusmn;")
	str = Replace(str, "µ", "&micro;")
	str = Replace(str, "¶", "&para;")
	str = Replace(str, "·", "&middot;")
	str = Replace(str, "¢", "&cent;")
	str = Replace(str, "£", "&pound;")
	str = Replace(str, "¥", "&yen;")
	str = Replace(str, "¼", "&frac14;")
	str = Replace(str, "½", "&frac12;")
	str = Replace(str, "¾", "&frac34;")
	str = Replace(str, "¹", "&sup1;")
	str = Replace(str, "²", "&sup2;")
	str = Replace(str, "³", "&sup3;")
	str = Replace(str, "¿", "&iquest;")
	str = Replace(str, "°", "&deg;")
	str = Replace(str, "¦", "&brvbar;")
	str = Replace(str, "§", "&sect;")
	str = Replace(str, "«", "&laquo;")
	str = Replace(str, "»", "&raquo;")
	CleanDodgyChars = str
End Function

Open in new window

0
 
LVL 6

Author Comment

by:worthyking1
ID: 35194982
@rhawk the DB field is NVARCHAR
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:rhawk
ID: 35195492
My next thought is that your content type on the page is either not set or wrong. Try each of these and see if one work:

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

<meta http-equiv="Content-Type" content="text/html; charset=windows-1251">


My thought is that the first and/or second will work. Let me know.

Also, can you point me to your web page so I can see it and view the source?
0
 
LVL 6

Author Comment

by:worthyking1
ID: 35197757
Currently the page has UTF-8 content type set.  Here's a sample page: http://dizzy.com/aibu/catalog_class.asp?clid=139
0
 
LVL 2

Expert Comment

by:rhawk
ID: 35198305
Okay, I found 2 web sites that will help a lot. One of them IF you have to encode the characters to HTML special characters and the other talks about the special characters in web pages.

http://www.alanwood.net/unicode/unicode_samples.html
http://www.utexas.edu/learn/html/spchar.html
0
 
LVL 2

Expert Comment

by:rhawk
ID: 35198380
Reading more of one of those links... What font and/or font family is the CSS setting the page to? Try one that is friendly to the special characters. We KNOW that this page on Experts-Exchange allowed you to enter "Pérez or Migüel" so we KNOW they are using a font or family that allows it. Try lucida sans and see if that does it.
0
 
LVL 1

Accepted Solution

by:
MikeK70 earned 2000 total points
ID: 35225903
1. Make sure the .asp file is saved in UTF-8 encoding
2. Make sure you have the code page for asp set correctly, your first line should read: <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
3. Make sure you have the encoding set correctly in the http header: <meta http-equiv="Content-Type" content="text/html; charset=utf-8">

With these 3 steps, no further HTML encoding of the characters should be necessary.

Cheers,

Mike
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

764 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