{"id":98,"date":"2011-07-08T16:50:24","date_gmt":"2011-07-08T22:50:24","guid":{"rendered":"http:\/\/www.businesstechnologyassociates.com\/blog\/?p=98"},"modified":"2011-07-08T16:54:06","modified_gmt":"2011-07-08T22:54:06","slug":"mysql-and-utf-8","status":"publish","type":"post","link":"https:\/\/www.businesstechnologyassociates.com\/blog\/2011\/07\/mysql-and-utf-8\/","title":{"rendered":"MySQL and UTF-8"},"content":{"rendered":"<p>I recently had a problem in a web application that I created where the UTF-8 characters were not interpreted correctly by browsers.\u00a0 The biggest issue was this did not happen in all instances of presenting these UTF-8 strings.<\/p>\n<p>After tracing the strings through several libraries, I found the culprit.\u00a0 The following SQL statement was the source of the text.<\/p>\n<pre>SELECT ID, CONCAT(sDescription, ' (', ID, ')') FROM ProdFamily;<\/pre>\n<p>Where ID is a integer key for the table and sDescription is a varchar column.\u00a0 The result of the CONCAT function is a &#8220;binary string&#8221; because of the integer column as one of the operands.\u00a0 The end result is that this &#8220;binary string&#8221; was treated differently than a regular UTF-8 string and characters outside the normal ASCII ones were not display correctly.\u00a0 To fix this issue the CAST function must be added to set the type of the ID column to string as follows.<\/p>\n<pre>SELECT ID, CONCAT(sDescription, ' (', CAST(ID AS CHAR), ')') FROM ProdFamily<\/pre>\n<p>See MySQL <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/string-functions.html#function_concat\" target=\"_blank\">CONCAT<\/a> or <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/cast-functions.html#function_cast\" target=\"_blank\">CAST<\/a> function documentation for more information.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I recently had a problem in a web application that I created where the UTF-8 characters were not interpreted correctly by browsers.\u00a0 The biggest issue was this did not happen in all instances of presenting these UTF-8 strings. After tracing the strings through several libraries, I found the culprit.\u00a0 The following SQL statement was the &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/www.businesstechnologyassociates.com\/blog\/2011\/07\/mysql-and-utf-8\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;MySQL and UTF-8&#8221;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,3],"tags":[],"class_list":["post-98","post","type-post","status-publish","format-standard","hentry","category-mysql","category-technology"],"_links":{"self":[{"href":"https:\/\/www.businesstechnologyassociates.com\/blog\/wp-json\/wp\/v2\/posts\/98","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.businesstechnologyassociates.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.businesstechnologyassociates.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.businesstechnologyassociates.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.businesstechnologyassociates.com\/blog\/wp-json\/wp\/v2\/comments?post=98"}],"version-history":[{"count":5,"href":"https:\/\/www.businesstechnologyassociates.com\/blog\/wp-json\/wp\/v2\/posts\/98\/revisions"}],"predecessor-version":[{"id":103,"href":"https:\/\/www.businesstechnologyassociates.com\/blog\/wp-json\/wp\/v2\/posts\/98\/revisions\/103"}],"wp:attachment":[{"href":"https:\/\/www.businesstechnologyassociates.com\/blog\/wp-json\/wp\/v2\/media?parent=98"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.businesstechnologyassociates.com\/blog\/wp-json\/wp\/v2\/categories?post=98"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.businesstechnologyassociates.com\/blog\/wp-json\/wp\/v2\/tags?post=98"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}