{"id":840,"date":"2009-06-16T07:54:49","date_gmt":"2009-06-16T05:54:49","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=840"},"modified":"2009-09-27T14:08:16","modified_gmt":"2009-09-27T12:08:16","slug":"unwalking-a-string-with-group_concat","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/unwalking-a-string-with-group_concat","title":{"rendered":"Unwalking a string with GROUP_CONCAT"},"content":{"rendered":"

“Walking a string” is an SQL technique to convert a single value into multiple rows result set. For example, walking the string ‘hello’<\/strong> results with 5 rows, each of which contains a single character from the text.<\/p>\n

I’ll present a brief example of walking a string, and then show how to “unwalk” the string: do the reverse operation.<\/p>\n

To walk a string, an integers table is required (or this could be a good use for SeqEngine<\/a>):<\/p>\n

\n
CREATE TABLE `int_table` (\r\n  `int_col` int(11) NOT NULL,\r\n  PRIMARY KEY  (`int_col`)\r\n) ENGINE=MyISAM DEFAULT CHARSET=latin1\r\n\r\n-- ...\r\n-- INSERTS follow here\r\n-- ...\r\n\r\nmysql> SELECT * FROM int_table;\r\n+---------+\r\n| int_col |\r\n+---------+\r\n|       0 |\r\n|       1 |\r\n|       2 |\r\n|       3 |\r\n|       4 |\r\n|       5 |\r\n|       6 |\r\n|       7 |\r\n|       8 |\r\n|       9 |\r\n+---------+\r\n10 rows in set (0.00 sec)<\/pre>\n<\/blockquote>\n
To convert a string to rows of characters, we join the text with the integers table (we assume there are enough numbers for covering the length of the text):<\/p>\n
\n
mysql> SELECT\r\n         SUBSTRING(s, int_col+1, 1) AS c\r\n       FROM int_table, (SELECT 'hello' AS s) sel1\r\n       WHERE int_col < char_length(s);\r\n+---+\r\n| c |\r\n+---+\r\n| h |\r\n| e |\r\n| l |\r\n| l |\r\n| o |\r\n+---+\r\n5 rows in set (0.00 sec)<\/pre>\n<\/blockquote>\n
More on this can be found in the excellent SQL Cookbook<\/a>.<\/p>\n
Unwalking the string<\/h4>\n
Doing the inverse action – combining the string back from the multiple rows, can be easily done using GROUP_CONCAT<\/strong>. It’s interesting to learn that GROUP_CONCAT<\/strong> does not actually require any GROUP BY<\/strong> clause. When no such clause is provided in the SQL query, all searched rows are used.<\/p>\n
Let’s assume now that we have a table of character values, which we want to concatenate back to a complete string. We can easily build this table:<\/p>\n
\n
CREATE TABLE characters AS\r\n  SELECT\r\n    SUBSTRING(s, int_col+1, 1) AS c\r\n  FROM int_table, (SELECT 'hello' AS s) sel1\r\n  WHERE int_col < char_length(s);<\/pre>\n<\/blockquote>\n
To reconstruct the text, we simply use MySQL’s GROUP_CONCAT<\/strong> with an empty separator:<\/p>\n
\n
mysql> SELECT GROUP_CONCAT(c separator '') AS s FROM characters;\r\n+-------+\r\n| s     |\r\n+-------+\r\n| hello |\r\n+-------+\r\n1 row in set (0.00 sec)<\/pre>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"
“Walking a string” is an SQL technique to convert a single value into multiple rows result set. For example, walking the string ‘hello’ results with 5 rows, each of which contains a single character from the text. I’ll present a brief example of walking a string, and then show how to “unwalk” the string: do […]<\/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":[45,21],"class_list":["post-840","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-books","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-dy","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/840","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=840"}],"version-history":[{"count":10,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/840\/revisions"}],"predecessor-version":[{"id":1352,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/840\/revisions\/1352"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=840"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=840"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=840"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}