{"id":4528,"date":"2012-06-12T07:28:08","date_gmt":"2012-06-12T05:28:08","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=4528"},"modified":"2012-06-12T07:28:08","modified_gmt":"2012-06-12T05:28:08","slug":"char-and-spaces","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/char-and-spaces","title":{"rendered":"CHAR and spaces"},"content":{"rendered":"

I know about it, I knew about it all along, but… it’s so easy to fall for it; there’s just so much absurdity!<\/p>\n

A CHAR<\/strong> type has a known number of characters. For example, the column:<\/p>\n

\n
CountryCode CHAR(3) CHARSET ascii NOT NULL<\/pre>\n<\/blockquote>\n
– is known to have exactly three characters. These could be ‘USA’<\/strong>, ‘FRA’<\/strong>, etc.<\/p>\n
What happens with spaces? A SQL CHAR<\/strong> type ignores any trailing spaces; thus, the code ‘GB ‘<\/strong> (the characters ‘G’<\/strong>, ‘B’<\/strong>, and the space ‘ ‘<\/strong>) is interpreted as ‘GB’<\/strong>. Trailing spaces are not regarded as part of the text. Want to see some absurdity?<\/p>\n
\n
CREATE TABLE `c_test` (\r\n\u00a0 `c` char(1) DEFAULT NULL\r\n);\r\nINSERT INTO c_test VALUES ('a');\r\nINSERT INTO c_test VALUES ('b');\r\nINSERT INTO c_test VALUES (' ');\r\n\r\nSELECT c, LENGTH(c), c = 'a', c = 'b', c = ' ', LENGTH(' ') FROM c_test;\r\n+------+-----------+---------+---------+---------+-------------+\r\n| c\u00a0\u00a0\u00a0 | LENGTH(c) | c = 'a' | c = 'b' | c = ' ' | LENGTH(' ') |\r\n+------+-----------+---------+---------+---------+-------------+\r\n| a\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n| b\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n+------+-----------+---------+---------+---------+-------------+<\/pre>\n<\/blockquote>\n
Note that LENGTH(c)<\/strong> is zero on third row, but nevertheless it equals the space character (c = ‘ ‘<\/strong>). Although, the length of the space character is… 1<\/strong> (and this is because the constant ‘ ‘<\/strong> is interpreted as VARCHAR)<\/strong>.<\/p>\n
To emphasize the absurdity, now take a closer look:<\/p>\n
\n
SELECT c, LENGTH(c), c = ' ', LENGTH(' '), c = '', LENGTH('') FROM c_test;\r\n+------+-----------+---------+-------------+--------+------------+\r\n| c\u00a0\u00a0\u00a0 | LENGTH(c) | c = ' ' | LENGTH(' ') | c = '' | LENGTH('') |\r\n+------+-----------+---------+-------------+--------+------------+\r\n| a\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n| b\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n+------+-----------+---------+-------------+--------+------------+<\/pre>\n<\/blockquote>\n
I add a comparison with the empty string ”<\/strong>, and it turns out the space character equals both a space character and the empty string, though their lengths do not agree.<\/p>\n
Now, I usually don’t bother with CHAR<\/strong> types, and prefer the VARCHAR<\/strong> or TEXT<\/strong> types, but every once in a while I do use it. Most recently, in a stored routine code in common_schema<\/a>, I iterate & parse some text. I iterated the text char by char, and declared a local CHAR(1)<\/strong> variable to assign iterated value to. It sure bit me when I got to a space character which insisted it was the empty text (indicating end of iteration).<\/p>\n
Of course the immediate and correct solution was to change the local variable’s type to VARCHAR(1)<\/strong> instead. But the time it consumed to find the problem… Be warned and be aware!<\/p>\n","protected":false},"excerpt":{"rendered":"
I know about it, I knew about it all along, but… it’s so easy to fall for it; there’s just so much absurdity! A CHAR type has a known number of characters. For example, the column: CountryCode CHAR(3) CHARSET ascii NOT NULL – is known to have exactly three characters. These could be ‘USA’, ‘FRA’, […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[18],"class_list":["post-4528","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-data-types"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1b2","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4528","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=4528"}],"version-history":[{"count":10,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4528\/revisions"}],"predecessor-version":[{"id":4946,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4528\/revisions\/4946"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=4528"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=4528"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=4528"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}