Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-03-22
8
Medium Priority
?
633 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
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/…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

604 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