blocks|key|2335793|text|好吧..。首先，让我向你们介绍亚当·马查尼奇，他教会了我关于数字表格的知识。他还使用这个数字表编写了一个非常快速的拆分函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2335794|http://dataeducation.com/counting-occurrences-of-a-substring-within-a-string/|offset|length|2335795|在实现了一个返回表的Split函数之后，您就可以对它进行连接并获得您想要的结果。|2335796|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|25|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@$D|Q|E|R|1|S]]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|C]]]]

Well... let me first introduce you to Adam Machanic who taught me about a Numbers table. He's also written a very fast split function using this Numbers table.

<a href="http://dataeducation.com/counting-occurrences-of-a-substring-within-a-string/" rel="nofollow noreferrer">http://dataeducation.com/counting-occurrences-of-a-substring-within-a-string/</a>

After you implement a Split function that returns a table, you can then join against it and get the results you want.

blocks|key|1316559|text|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+%2B+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+%2B+@spliter,+n)+-+n)+AS+element
FROM++++dbo.Users
++++JOIN+Nums
++++ON+n+<=+DATALENGTH(member)+%2B+1
++++AND+SUBSTRING(@spliter+%2B+member,+n,+1)+=+@spliter
ORDER+BY+id
OPTION+(MAXRECURSION+0);+--Nums+CTE+is+generated+recursively,+we+don't+want+to+limit+recursion+count.|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1316560|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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 &lt; 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 &lt;= 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.
</code></pre>

blocks|key|2335823|text|这是可以设计出的最短且可读的字符串到行的分割器，也可以是。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|2335824|选择纯CTE代替function的用例，例如不允许在数据库上创建function+:-)|2335825|通过函数创建行生成器(可以使用loop或via+CTE实现)仍然需要使用横向联接(DB2和Sybase具有此功能，使用lateral关键字；在SQL+Server中，这类似于CROSS+APPLY和OUTER+APPLY)，以最终将函数生成的拆分行联接到主表。|2335826|纯CTE方法可能比函数方法更快。不过，速度指标在于性能分析，如果这确实比其他解决方案更快，那么只需检查它的执行计划：|2335827|with+Pieces(theId,+pn,+start,+stop)+AS
(
++++++SELECT+id,+1,+1,+charindex(';',+member)
++++++from+tbl

++++++UNION+ALL

++++++SELECT+id,+pn+%2B+1,+stop+%2B+1,+charindex(';',+member,+stop+%2B+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+|code-block|syntax|javascript|2335828|输出：|2335829|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)|2335830|基础逻辑来源：T-SQL:+Opposite+to+string+concatenation+-+how+to+split+string+into+multiple+records|2335831|实时测试：http://www.sqlfiddle.com/#!3/2355d/1|2335832|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco|1|http://www.sqlfiddle.com/#!3/2355d/1^0|S|1|0|0|18|0|0|3M|0|0|1M|0|0|0|3|0|0|0|2I|7|2B|0|0|0|15|5|10|1|0^^$0|@$1|2|3|4|5|6|7|18|8|@$9|19|A|1A|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1B|8|@$9|1C|A|1D|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|1E|8|@$9|1F|A|1G|B|C]]|D|@]|E|$]]|$1|J|3|K|5|6|7|1H|8|@$9|1I|A|1J|B|C]]|D|@]|E|$]]|$1|L|3|M|5|N|7|1K|8|@]|D|@]|E|$O|P]]|$1|Q|3|R|5|6|7|1L|8|@$9|1M|A|1N|B|C]]|D|@]|E|$]]|$1|S|3|T|5|N|7|1O|8|@]|D|@]|E|$O|P]]|$1|U|3|V|5|6|7|1P|8|@$9|1Q|A|1R|B|C]]|D|@$9|1S|A|1T|1|1U]]|E|$]]|$1|W|3|X|5|6|7|1V|8|@$9|1W|A|1X|B|C]]|D|@$9|1Y|A|1Z|1|20]]|E|$]]|$1|Y|3|-4|5|6|7|21|8|@]|D|@]|E|$]]]|Z|$10|$5|11|12|13|E|$14|15]]|16|$5|11|12|13|E|$14|17]]]]

This is the shortest and readable string-to-rows splitter one could devise, and could be faster too.

Use case of choosing pure CTE instead of function, e.g. when you're not allowed to create a function on database :-) 

Creating rows generator via function(which could be implemented by using loop or via CTE too) shall still need to use lateral joins(DB2 and Sybase have this functionality, using LATERAL keyword; In SQL Server, this is similar to CROSS APPLY and OUTER APPLY) to ultimately join the splitted rows generated by a function to the main table.

Pure CTE approach could be faster than function approach. The speed metrics lies in profiling though, just check the execution plan of this compared to other solutions if this is indeed faster:

<pre><code>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 &gt; 0
)
select 

 t.id, t.name, 

 word = 
 substring(t.member, p.start, 
 case WHEN stop &gt; 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 
</code></pre>

Output:

<pre><code>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)
</code></pre>

Base logic sourced here: <a href="https://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco">T-SQL: Opposite to string concatenation - how to split string into multiple records</a>

Live test: <a href="http://www.sqlfiddle.com/#!3/2355d/1" rel="nofollow noreferrer">http://www.sqlfiddle.com/#!3/2355d/1</a>

Assume we have the following table:

<pre><code>id name member
1 jacky a;b;c
2 jason e
3 kate i;j;k
4 alex null
</code></pre>

Now I want to use the sql or t-sql to return the following table:

<pre><code>1 jacky a
1 jacky b
1 jacky c
2 jason e
3 kate i
......
</code></pre>

How to do that?
I'm using the MSSQL, MYSQL and Oracle database.

How to split the string value in one column and return the result table

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

假设我们有下表：id name   member1  jacky  a;b;c2  jason  e3  kate   i;j;k4  alex   null现在我想使用sql或t-sql返回下表：1 jacky a1 jacky b1 jacky c2 jason e3 kate  i......如何做到这一点？我使...

问如何将字符串值拆分为一列并返回结果表
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将字符串值拆分为一列并返回结果表EN