字符串拆分，一行变多行
【问题】
I’d like to extract certain snippets from a MySQL column which are bounded in some unique way. Here is an example:
I have the following table (TableA):
id | column_a
—+———
1 | There is a user [u?u=990] and another [u?u=5458855].
2 | And here is just one user [u?u=9390].
3 | And here is nothing.
And I want to get this:
id | result_a
—+———
1 | 990
1 | 5458855
2 | 9390
At the moment I have this query:
SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(column_a, ‘[u?u=’, -1), ‘]’, 1) AS result_a FROM TableA
But that gives me:
id | result_a
—+———
1 | 5458855
2 | 9390
别人给出答案，但不一定正确：
SELECT id, SUBSTRING_INDEX(
SUBSTRING_INDEX(column_a, ‘[u?u=’, -1 - n.v)
, ‘]’, 1) AS result_a
FROM TableA JOIN (
SELECT b6.v | b5.v | b4.v | b3.v | b2.v | b1.v | b0.v AS v
FROM (
SELECT 0 v UNION ALL SELECT 1<<0
) b0 JOIN (
SELECT 0 v UNION ALL SELECT 1<<1
) b1 JOIN (
SELECT 0 v UNION ALL SELECT 1<<2
) b2 JOIN (
SELECT 0 v UNION ALL SELECT 1<<3
) b3 JOIN (
SELECT 0 v UNION ALL SELECT 1<<4
) b4 JOIN (
SELECT 0 v UNION ALL SELECT 1<<5
) b5 JOIN (
SELECT 0 v UNION ALL SELECT 1<<6
) b6
) n
WHERE RIGHT(column_a, 5+CHAR_LENGTH(
SUBSTRING_INDEX(column_a, ‘[u?u=’, -1 - n.v)
))REGEXP ‘^\\[u\?u=.*\\]’
【回答】
既要处理字符串拆分，又要将一条变多条记录，在 SQL 中计算很麻烦，这种情况用 SPL 就很简单，脚本如下：
A |
|
1 |
$select id,column_a from TableA |
2 |
=A1.run(column_a=column_a.split@b("u?u=").to(2,).(parse@n (~))) |
3 |
=A2.news(column_a; A2.id:id, ~:result_a) |
A1：读取 TableA 表
A2：从 column_a 列中拆出子串集合
A3：生成新序表
例子程序：
文件解析后逆分组扩展
oracle 如何把单行记录拆分为多行
一条记录到多条记录的转置怎么写
将数据库中的一列字符拆分成多行
逆分组计算
拆字符串成多记录
字符串拆分一行变多行
