6,129
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :DBA_Huangzj
-- Date :2013-07-02 07:57:49
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[aaa]
if object_id('[aaa]') is not null drop table [aaa]
go
create table [aaa]([aid] int,[name] varchar(14))
insert [aaa]
select 1,'王为,赵立,卫名' union all
select 2,'张三,李四'
--------------开始查询--------------------------
select
[aid],
SUBSTRING(a.name,number,CHARINDEX(',',a.name+',',number)-number) as name
from
[aaa] a,master..spt_values
where
number >=1 and number<len(a.name)
and type='p'
and substring(','+a.name,number,1)=','
----------------结果----------------------------
/*
aid name
----------- --------------
1 王为
1 赵立
1 卫名
2 张三
2 李四
*/