Solved

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

Posted on 2011-03-22
8
619 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 500 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

728 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