RE: special characters

[Park, Simon]

> When trying to add characters like � and �, my SQL 7 
> server tells me I have not enclosed my strings properly. 
> I'm assuming the tilde is breaking it. Any way around 
> that, other than looking for that character and replacing 
> it, with say the HTML equivalent that works? Or will I
> just have to do a replace() on the string?

If you are using CF 5, a quick way to replace special characters (like smart
quotes when cutting and pasting from Word) is to use user-defined functions.
The one we adapted from a couple of sources is this:

<CFSCRIPT>
function SafeChars(textin) {
        
bad_chars="(tm),�,"",",",',',-,-,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�
,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�
,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�,�";

        
good_chars="&##153;,&nbsp;,&quot;,&quot;,&quot;,&lsquo;,&rsquo;,&mdash;,&nda
sh;,&AElig;,&Aacute;,&Acirc;,&Agrave;,&Aring;,&Atilde;,&Auml;,&Ccedil;,&ETH;
,&Eacute;,&Ecirc;,&Egrave;,&Euml;,&Iacute;,&Icirc;,&Igrave;,&Iuml;,&Ntilde;,
&Oacute;,&Ocirc;,&Ograve;,&Oslash;,&Otilde;,&Ouml;,&THORN;,&Uacute;,&Ucirc;,
&Ugrave;,&Uuml;,&Yacute;,&aacute;,&acirc;,&aelig;,&agrave;,&aring;,&atilde;,
&auml;,&ccedil;,&eacute;,&ecirc;,&egrave;,&eth;,&euml;,&iacute;,&icirc;,&igr
ave;,&iuml;,&ntilde;,&oacute;,&ocirc;,&ograve;,&oslash;,&otilde;,&ouml;,&szl
ig;,&thorn;,&uacute;,&ucirc;,&ugrave;,&uuml;,&yacute;,&yuml;,&iexcl;,&pound;
,&curren;,&yen;,&brvbar;,&sect;,&uml;,&copy;,&ordf;,&laquo;,&not;,&shy;,&reg
;,&macr;,&deg;,&plusmn;,&sup2;,&sup3;,&acute;,&micro;,&para;,&middot;,&cedil
;,&sup1;,&ordm;,&raquo;,&frac14;,&frac12;,&frac34;,&iquest;,&times;,&divide;
,&cent;";

        /* Replace Content with updated Content */
        text_out=ReplaceList(textin, bad_chars, good_chars);
  return text_out;
}
</CFSCRIPT>

You can put this UDF at the top of the page (or include it if you want to
use it multiple times) and then use a SQL statement like this:

INSERT INTO mytable
                (myfield1, myfield2)
VALUES
                ('#SafeChars(mytext1)#', '#SafeChars(mytext2)#')


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Simon Park              Computer Systems Management, Inc.
Director, E-Business    205 South Whiting Street #201
Ph: 703-823-4300 x119   Alexandria, VA  22304
fax: 703-823-4301       http://www.csmi.com  
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists