coderMe coderMe - 16 days ago 5
SQL Question

MYSQL/Coldfusion replace registration symbol not working

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>


Screenshot of query cfdump

Is there a better way to approach this? I originally did this in Coldfusion using
<cfset getStaff.name = Replace(getStaff.name,Chr(174),'<sup>&reg;</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.

Answer

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. Try one of the following:

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.