拆分字符串

来源：互联网发布：免费企业建站cms 编辑：程序博客网时间：2024/04/19 09:10

IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb (id INT,col VARCHAR(30))
INSERT INTO tb VALUES (1,'aa,bb')
INSERT INTO tb VALUES (2,'aaa,bbb,ccc')
go
--1.2000/2005通用方法
SELECT
a.id,
col=SUBSTRING(a.col,number,CHARINDEX(',',a.col+',',number)-b.number)
FROM tb a
JOIN master..spt_values b
ON b.type='p'
--AND SUBSTRING(','+a.col,b.number,1)=',' --用此条件或下面的条件均可
AND CHARINDEX(',',','+a.col,number)=number

--2.2005以上新方法:
SELECT a.id,b.col
FROM (SELECT id,col=CAST('<v>'+REPLACE(col,',','</v><v>')+'</v>' AS xml) FROM tb) a
OUTER APPLY (SELECT col=T.C.value('.','varchar(50)') FROM a.col.nodes('/v') AS T(C)) b

--结果:
/*
id col
----------- --------------------------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc

(5 行受影响)
*/

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wufeng4552/archive/2009/09/09/4534365.aspx