如何将字符串值拆分为一列并返回结果表
假设我们有下表：
id name member
1 jacky a;b;c
2 jason e
3 kate i;j;k
4 alex null现在我想使用sql或t-sql返回下表：
1 jacky a
1 jacky b
1 jacky c
2 jason e
3 kate i
......如何做到这一点？我使用的是MSSQL、MYSQL和Oracle数据库。
回答 3
Stack Overflow用户
发布于 2012-05-08 12:37:24
这是可以设计出的最短且可读的字符串到行的分割器，也可以是。
选择纯CTE代替function的用例，例如不允许在数据库上创建function :-)
通过函数创建行生成器(可以使用loop或via CTE实现)仍然需要使用横向联接(DB2和Sybase具有此功能，使用lateral关键字；在SQL Server中，这类似于CROSS APPLY和OUTER APPLY)，以最终将函数生成的拆分行联接到主表。
纯CTE方法可能比函数方法更快。不过，速度指标在于性能分析，如果这确实比其他解决方案更快，那么只需检查它的执行计划：
with Pieces(theId, pn, start, stop) AS
(
SELECT id, 1, 1, charindex(';', member)
from tbl
UNION ALL
SELECT id, pn + 1, stop + 1, charindex(';', member, stop + 1)
from tbl
join pieces on pieces.theId = tbl.id
WHERE stop > 0
)
select
t.id, t.name,
word =
substring(t.member, p.start,
case WHEN stop > 0 THEN p.stop - p.start
ELSE 512
END)
from tbl t
join pieces p on p.theId = t.id
order by t.id, p.pn 输出：
ID NAME WORD
1 jacky a
1 jacky b
1 jacky c
2 jason e
3 kate i
3 kate j
3 kate k
4 alex (null)基础逻辑来源：T-SQL: Opposite to string concatenation - how to split string into multiple records
实时测试：http://www.sqlfiddle.com/#!3/2355d/1
Stack Overflow用户
发布于 2012-05-08 10:41:58
好吧..。首先，让我向你们介绍亚当·马查尼奇，他教会了我关于数字表格的知识。他还使用这个数字表编写了一个非常快速的拆分函数。
http://dataeducation.com/counting-occurrences-of-a-substring-within-a-string/
在实现了一个返回表的Split函数之后，您就可以对它进行连接并获得您想要的结果。
Stack Overflow用户
发布于 2012-05-08 10:59:40
IF OBJECT_ID('dbo.Users') IS NOT NULL
DROP TABLE dbo.Users;
CREATE TABLE dbo.Users
(
id INT IDENTITY NOT NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
member VARCHAR(1000)
)
GO
INSERT INTO dbo.Users(name, member) VALUES
('jacky', 'a;b;c'),
('jason', 'e'),
('kate', 'i;j;k'),
('alex', NULL);
GO
DECLARE @spliter CHAR(1) = ';';
WITH Base AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM Base
WHERE n < CEILING(SQRT(1000)) --generate numbers from 1 to 1000, you may change it to a larger value depending on the member column's length.
)
, Nums AS --Numbers Common Table Expression, if your database version doesn't support it, just create a physical table.
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS n
FROM Base AS B1 CROSS JOIN Base AS B2
)
SELECT id,
SUBSTRING(member, n, CHARINDEX(@spliter, member + @spliter, n) - n) AS element
FROM dbo.Users
JOIN Nums
ON n <= DATALENGTH(member) + 1
AND SUBSTRING(@spliter + member, n, 1) = @spliter
ORDER BY id
OPTION (MAXRECURSION 0); --Nums CTE is generated recursively, we don't want to limit recursion count.https://stackoverflow.com/questions/10491784
复制相似问题
腾讯云开发者
