“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)
I was just thinking of writing a SP.
Thanks a lot for sharing this.
I selected the int_col because I wanted the exact position of the character.
I forgot to mention one thing about group_concat. I am getting 54 strings as against expected 74 when I use group_concat. I do not have access to my.cnf file so I changed the session variable but it did not help.
SET SESSION group_concat_max_len = 1000000;
Hi Shantanu,
do you get any warnings? If group_concat_max_len really is too short to hold the result, you should get a warning:
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+———+——+————————————–+
| Level | Code | Message |
+———+——+————————————–+
| Warning | 1260 | 1 line(s) were cut by GROUP_CONCAT() |
+———+——+————————————–+
If you want to be sure, don’t set group_concat_max_len to a particular length, just set it to max_allowed_packet. This is the practical maximum of any string length and if you exceed that you will get an entirely different error message, see: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html
Yes, I got exactly the same warning and after following your tip, I got the results. Thanks.