“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 the reverse operation.
To walk a string, an integers table is required (or this could be a good use for SeqEngine):
CREATE TABLE `int_table` ( `int_col` int(11) NOT NULL, PRIMARY KEY (`int_col`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -- ... -- INSERTS follow here -- ... mysql> SELECT * FROM int_table; +---------+ | int_col | +---------+ | 0 | | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | +---------+ 10 rows in set (0.00 sec)
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):
mysql> SELECT SUBSTRING(s, int_col+1, 1) AS c FROM int_table, (SELECT 'hello' AS s) sel1 WHERE int_col < char_length(s); +---+ | c | +---+ | h | | e | | l | | l | | o | +---+ 5 rows in set (0.00 sec)
More on this can be found in the excellent SQL Cookbook.
Unwalking the string
Doing the inverse action – combining the string back from the multiple rows, can be easily done using GROUP_CONCAT. It’s interesting to learn that GROUP_CONCAT does not actually require any GROUP BY clause. When no such clause is provided in the SQL query, all searched rows are used.
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:
CREATE TABLE characters AS SELECT SUBSTRING(s, int_col+1, 1) AS c FROM int_table, (SELECT 'hello' AS s) sel1 WHERE int_col < char_length(s);
To reconstruct the text, we simply use MySQL’s GROUP_CONCAT with an empty separator:
mysql> SELECT GROUP_CONCAT(c separator '') AS s FROM characters; +-------+ | s | +-------+ | hello | +-------+ 1 row in set (0.00 sec)
Nice article.
The SET SESSION group_concat_max_len = 1000000 was exactly the info I required.
I had trouble with this issue as follows:-
I used group_concat() in isolation and no truncation occured.
I used the same “isolated” group_concat() query as a subquery and joined to it. Then truncation occurred but NO WARNING/ERROR.
I moved the subquery into a temp table and then joined on that. Then, the now infamous 1260 ERROR was thrown.
Needless to say, problem solved!