1. 将字符串分解成几行(Walking a string)
方法:首先需要一个interger表
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)
|
然后进行select
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)
|
2. 将列值拼接成一个字符串(unwalking a string)
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);
|
mysql> SELECT GROUP_CONCAT(c separator '') AS s FROM characters; +-------+ | s | +-------+ | hello | +-------+ 1 row in set (0.00 sec)
|
阅读(4977) | 评论(0) | 转发(0) |