T-SQL：与字符串连接相反-如何将字符串拆分为多个记录
技术问答
378 人阅读
|
0 人回复
|
2023-09-14
|
|
我想知道您将如何解决相反的问题：将逗号分隔的字符串拆分为数据行：
7 J" o5 [$ t- H可以说我有表：( Z- p& o9 ^ Q" E* [0 F
userTypedTags(userID,commaSeparatedTags) 'one entry per user
: m% Z; S' P0 c% Y* ztags(tagID,name)9 K* m; i+ \- h. _+ i
( P1 J/ \1 Z$ e并想将数据插入表中
6 R* F/ P7 A6 l% _# ^! \1 `& auserTag(userID,tagID) 'multiple entries per user, d2 @% M$ ~! w5 \/ \
& S, r. E$ W& A0 O
解决方案:
' c, E( C d' |- S! @- f# Z/ p
' e- R9 t2 g( @6 a# |4 y3 i3 r2 M$ K3 y7 r& K9 H) J8 D
* e- ^+ j2 X9 B 这个问题的解决方案记录在这里，包括这个小gem：* E& S6 i- ]6 {9 q _& w
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512)); Z8 O( m7 x% @" q$ K: Y3 @" w
RETURNS table
; I2 v" x% V {AS3 ]% M; ] @0 H1 B+ g
RETURN (- @) b6 ^, {) D% t- T
WITH Pieces(pn, start, stop) AS (
; _" t: w4 k- @9 x, h* K SELECT 1, 1, CHARINDEX(@sep, @s)0 j$ M; h3 j( o" k: s
UNION ALL
; S2 H9 D* C5 K9 D# w7 P% V SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)5 p$ Z" E2 Y' k) d( g4 s; Z/ ?' }
FROM Pieces; ]. H0 K( L. W0 F3 L3 c5 L
WHERE stop > 0
( E3 I, K7 t: h3 g. S )
2 o/ Z2 [( Q- e5 |) V7 y) Z SELECT pn,# X6 c0 P! T: l8 ^( V! [ i
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
2 S! W* d' X9 g2 t FROM Pieces0 m# Y* K: S- r! B2 ?+ B
) |
|
|
|
|
|
|
|
|
|
