MYSQL/Coldfusion replace registration symbol not working

Question

I'd like to make all registration symbols superscript by wrapping them with a <sup> HTML tag. So, I can do this in SQL no problem:

SELECT s.id,
Replace(s.name,'®','<sup>&reg;</sup>') AS name
FROM staff s
WHERE name LIKE '%®%'

Result:

id | name
 1 | Name1 CFP<sup>&reg;</sup>, CDFA
 2 | Jeffrey test CFP<sup>&reg;</sup>
 3 | Matthew hello CFP<sup>&reg;</sup> CFA

But when I run it in Coldfusion from a cfquery tag, it looks as if the ® character is interpreted as Â®.

<cfquery name="getStaff" dataSource="#this.dsn#">
    SELECT s.id,
    Replace(s.name,'®','<sup>&reg;</sup>') AS name
    FROM staff s
    WHERE 1=1
    <cfif isDefined("arguments.permalink")>
        AND s.permalink=<cfqueryparam value="#arguments.permalink#" />
    </cfif>
</cfquery>

Is there a better way to approach this? I originally did this in Coldfusion using <cfset getStaff.name = Replace(getStaff.name,Chr(174),'<sup>®</sup>') />, which worked fine until I switched to Mustache templating.

I'd definitely prefer to use the CHAR() function if I could figure out what numeric character ® is in Mysql. (Note, using utf8_general_ci on this and all DB tables) I tried CHAR(174) in Mysql, but it won't work because (as far as I can tell) Mysql isn't using the same character set - SELECT CHAR(174) returns a blob.

Try using some thing like: DECLARE r nchar(1) = . I am thing it is a utf-8 vs utf-16 thing. You may have to save the .cfm file as a utf-16 too — James A Mohler, Nov 23 '16 at 17:32
Is the symbol *hard-coded* into the .cfm script? If so, it is probably an issue with the page encoding. Try a) adding `` at the top of the script OR b) use the CF function, ie `#chr(174)#`. — Leigh, Nov 23 '16 at 17:33
Side note, it is a good practice to always specify a `cfsqltype`. If omitted, it defaults to CF_SQL_CHAR, which may force an implicit conversion and cause wrong/unintended results. — Leigh, Nov 23 '16 at 17:57
@JamesAMohler I'm not sure why, but I get a `You have an error in your SQL syntax... near 'DECLARE r nchar(1) = 'Â®' SELECT s.id,...'` error. I think it should be `DECLARE @r`, but even then it throws the same error. — coderMe, Nov 23 '16 at 18:02
@Leigh The `cfprocessingdirective` did not work for me, possibly because I can't figure out how to add it to a ``. (I got a function name conflict with my component's `start()` function.) Option B worked perfectly for me - `Replace(s.name,'#chr(174)#','^®') AS name` - could you make this an answer and I'll accept? — coderMe, Nov 23 '16 at 18:06
*Mysql isn't using the same character set* Heh, you and James may be right. On my install I had to use latin1 or convert from utf16 to utf8, ie `SELECT CHAR(174 USING latin1) , CAST(CHAR(174 USING utf16) AS CHAR CHARACTER SET utf8)` — Leigh, Nov 23 '16 at 23:28

score 2 · Accepted Answer · edited May 23 '17 at 12:30

UPDATE:

I'd definitely prefer to use the CHAR() function if I could figure out what numeric character ® is in Mysql. (Note, using utf8_general_ci on this and all DB tables) I tried CHAR(174) in Mysql, but it won't work because (as far as I can tell) Mysql isn't using the same character set - SELECT CHAR(174) returns a blob.

As mentioned in the comments, it sounds like the default charset for your database is utf8. So presumably it failed because the decimal 174 is not the correct way to represent the registered sign in utf8. That symbol requires two bytes. Using the proper hex or decimal value for your default charset (ie utf8) it works as expected:

Hex: CHAR(0xC2AE)
Decimal: CHAR(194,174)

Though it would be better to specify the charset explicitly with USING:

Hex: CHAR(194,174 USING utf8)
Decimal: CHAR(0xC2AE USING utf8)

Is the symbol hard-coded into the .cfm script? If so, it is probably an issue with the character encoding of the script. When interpreting literal characters within the file, the page encoding is what matters. Try:

Adding <cfprocessingdirective pageEncoding="utf-8"> to the top of the script.
Note: For CFC's, the cfprocessingdirective tag must follow the cfcomponent tag
IF the default charset for your database is utf8, try using the CF equivalent function, ie #chr(174)#. However, IMO it is better to use the MySQL Char() function instead.

Side note about cfqueryparam, it is a good practice to always specify a cfsqltype. If omitted, it defaults to CF_SQL_CHAR, which may force implicit conversion and cause wrong/unintended results in some cases (numbers, dates, etcetera). Even for string values it is a good idea to specify the type, as there may be slight differences with how CHAR and VARCHAR types are treated on the database side.

Using `'#chr(174)#'` worked perfectly for me. And, I do *usually* use a `cfsqltype` for any non-varchar fields. — coderMe, Nov 24 '16 at 15:01
After further tests with different db charsets, I think it is better to use the MySQL function. See updated answer. — Leigh, Nov 24 '16 at 16:07

James A Mohler · Answer 2 · 2016-11-23T22:01:46.860

0

It is possible to do something like ColdFusion Char() in SQL

<cfquery name="getStaff" dataSource="#this.dsn#">
  SELECT s.id,
  REPLACE(s.name, CHAR(174), '<sup>&reg;</sup>') AS name
  FROM staff s
  WHERE 1=1
  <cfif isDefined("arguments.permalink")>
    AND s.permalink=<cfqueryparam value="#arguments.permalink#" />
  </cfif>
</cfquery>

For MySQL:

See: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_char

For SQL Server:

See: https://msdn.microsoft.com/en-us/library/ms187323.aspx

edited Nov 23 '16 at 22:01

answered Nov 23 '16 at 18:46

James A Mohler

11,060
15
46
72

Looks like a Microsoft reference for a MySQL question. – Dan Bracuk Nov 23 '16 at 18:57
*...it is a utf-8 vs utf-16 thing.* @JamesAMohler - Yes, the issue with the MySQL `char()` function does seem to be charset. The docs say `char()` returns binary. Converting the binary a string works as long as it represents a valid character in the default (or supplied) charset. The decimal 174 is valid for "utf16" or "latin1". For "utf8" it is apparently two bytes. Example: `SELECT CHAR(174 USING latin1) AS Latin1Value , CHAR(174 USING utf16) Utf16Value , CAST(CHAR(174 USING utf16) AS CHAR CHARACTER SET utf8) Utf16toUtf8Value , CHAR(194,174 USING utf8) AS Utf8Value` – Leigh Nov 24 '16 at 12:47
... or using with the [utf8 hex value, ie C2 AE](http://www.fileformat.info/info/unicode/char/AE/index.htm), ie `CHAR(0xC2AE USING utf8)` – Leigh Nov 24 '16 at 14:22
I mentioned in my answer that I already tried `CHAR(174)` in Mysql, and it returned a binary object. I also tested this exact line in my query, and it never replaced the character. I didn't know you could specify a character encoding in `CHAR()`. – coderMe Nov 24 '16 at 14:57
I got the same result. However, as you and James mentioned, the charset matters. The default charset for my database is utf8. So presumably it fails because the decimal 174 is not the correct way to represent the registered symbol in utf8. It requires two bytes. However, using the proper hex or decimal value for the default charset ie utf8 works fine, ie `CHAR(0xC2AE)` or `CHAR(194,174 USING utf8)` or `CHAR(0xC2AE USING utf8)` – Leigh Nov 24 '16 at 15:16

MYSQL/Coldfusion replace registration symbol not working

2 Answers2