mysql中字符串分解成行，及各行拼接成字符串的方法

amwdnuff

mysql中字符串分解成行，及各行拼接成字符串的方法
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 &#39;hello&#39; AS s) sel1
WHERE int_col < char_length(s);




mysql> SELECT GROUP_CONCAT(c separator &#39;&#39;) AS s FROM characters;
+-------+
| s |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)

amwdnuff

mysql中字符串分解成行，及各行拼接成字符串的方法
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 &#39;hello&#39; 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 &#39;hello&#39; AS s) sel1
WHERE int_col < char_length(s);




mysql> SELECT GROUP_CONCAT(c separator &#39;&#39;) AS s FROM characters;
+-------+
| s |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)

amwdnuff

mysql中字符串分解成行，及各行拼接成字符串的方法
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 &#39;hello&#39; 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 &#39;hello&#39; AS s) sel1
WHERE int_col < char_length(s);




mysql> SELECT GROUP_CONCAT(c separator &#39;&#39;) AS s FROM characters;
+-------+
| s |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)