如何把用分隔符隔开的一列数据拆分到新列中?
例如:
表列为: 1 2|3|4 5
拆分成列为:1 2|3|4 5 2 3 4
就是把第二列 变成新的 三列
-
SQL code
mysql> set @col='2|3|4'; Query OK, 0 rows affected (0.00 sec) mysql> select SUBSTRING_INDEX(@col,'|',1) as c1, -> SUBSTRING_INDEX(SUBSTRING_INDEX(@col,'|',2),'|',-1) as c2, -> SUBSTRING_INDEX(@col,'|',-1) as c3; +------+------+------+ | c1 | c2 | c3 | +------+------+------+ | 2 | 3 | 4 | +------+------+------+ 1 row in set (0.05 sec) mysql>