SQL字符串拆分问题

Question

有如下表,COL2是TEXT类型,内容用空格分开的
COL1    COL2
ABC     123  4568 1234565
DBD     1231 56455 5645665我想拆分如下
COL1    COL2
ABC     123  
ABC     4568 
ABC     1234565
DBD     1231
DBD     56455 
DBD     5645665不知怎么搞,请教高手

Answer 1

/*
标题：分解字符串并查询相关数据
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2008-03-18
地点：广东深圳
说明：通过使用函数等方法分解字符串查询相关数据。问题：通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。
例如 @str = '1,2,3',查询下表得到记录1,4,5,6
ID TypeID
1  1,2,3,4,5,6,7,8,9,10,11,12
2  2,3 
3  3,7,8,9 
4  2,6 
5  4,5
6  6,7 
*/
-----------------------------
create table tb (ID int , TypeID varchar(30)) 
insert into tb values(1 , '1,2,3,4,5,6,7,8,9,10,11,12') 
insert into tb values(2 , '2,3') 
insert into tb values(3 , '3,7,8,9') 
insert into tb values(4 , '2,6') 
insert into tb values(5 , '4,5')
insert into tb values(6 , '6,7')
go
-----------------------------
--如果仅仅是一个,如@str = '1'.
declare @str as varchar(30)
set @str = '1'
select * from tb where charindex(',' + @str + ',' , ',' + TypeID + ',') > 0
select * from tb where ',' + TypeID + ',' like '%,' + @str + ',%'
/*
ID          TypeID                         
----------- ------------------------------ 
1           1,2,3,4,5,6,7,8,9,10,11,12
（所影响的行数为 1 行）
*/-----------------------------
--如果包含两个,如@str = '1,2'.
declare @str as varchar(30)
set @str = '1,2'
select * from tb where charindex(',' + left(@str , charindex(',' , @str) - 1) + ',' , ',' + typeid + ',') > 0 or 
  charindex(',' + substring(@str , charindex(',' , @str) + 1 , len(@str)) + ',' , ',' + typeid + ',') > 0
select * from tb where ',' + typeid + ',' like '%,' + left(@str , charindex(',' , @str) - 1) + ',%' or 
  ',' + typeid + ',' like '%,' + substring(@str , charindex(',' , @str) + 1 , len(@str)) + ',%'
/*
ID          TypeID                         
----------- ------------------------------ 
1           1,2,3,4,5,6,7,8,9,10,11,12
2           2,3
4           2,6
（所影响的行数为 3 行）
*/-------------------------------------------
--如果包含三个或四个，用PARSENAME函数来处理.
declare @str as varchar(30)
set @str = '1,2,3,4'
select * from tb where 
  charindex(',' + parsename(replace(@str , ',' , '.') , 4) + ',' , ',' + typeid + ',') > 0 or
  charindex(',' + parsename(replace(@str , ',' , '.') , 3) + ',' , ',' + typeid + ',') > 0 or
  charindex(',' + parsename(replace(@str , ',' , '.') , 2) + ',' , ',' + typeid + ',') > 0 or
  charindex(',' + parsename(replace(@str , ',' , '.') , 1) + ',' , ',' + typeid + ',') > 0 
select * from tb where 
  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 4) + ',%' or
  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 3) + ',%' or
  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 2) + ',%' or
  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 1) + ',%'
/*
ID          TypeID                         
----------- ------------------------------ 
1           1,2,3,4,5,6,7,8,9,10,11,12
2           2,3
3           3,7,8,9
4           2,6
5           4,5
（所影响的行数为 5 行）
*/---------------------------------------
--如果超过四个，则只能使用函数或动态SQL来分解并查询数据。
/*
名称：fn_split函数.
功能：实现字符串分隔功能的函数
*/
create function dbo.fn_split(@inputstr varchar(8000), @seprator varchar(10))
returns @temp table (a varchar(200))
as 
begin
  declare @i int
  set @inputstr = rtrim(ltrim(@inputstr))
  set @i = charindex(@seprator , @inputstr)
  while @i >= 1
  begin
    insert @temp values(left(@inputstr , @i - 1))
    set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)
    set @i = charindex(@seprator , @inputstr)
  end
  if @inputstr <> '\'
  insert @temp values(@inputstr)
  return 
end
go--调用
declare @str as varchar(30)
set @str = '1,2,3,4,5'select distinct m.* from tb m,
(select * from dbo.fn_split(@str,',')) n
where charindex(',' + n.a + ',' , ',' + m.typeid + ',') > 0drop table tb
drop function dbo.fn_split /*
ID          TypeID                         
----------- ------------------------------ 
1           1,2,3,4,5,6,7,8,9,10,11,12
2           2,3
3           3,7,8,9
4           2,6
5           4,5
（所影响的行数为 5 行）
*/------------------------------------------
--使用动态SQL的语句。
declare @str varchar(200)
declare @sql as varchar(1000)
set @str = '1,2,3,4,5'
set @sql = 'select ''' + replace(@str , ',' , ''' as id union all select ''')
set @sql = @sql + ''''
set @sql = 'select distinct a.* from tb a , (' + @sql + ') b where charindex(' + ''','' + b.id + ' + ''',''' + ' , ' + ''','' + a.typeid + ' + ''',''' + ') > 0 '
exec (@sql)
/*
ID          TypeID                         
----------- ------------------------------ 
1           1,2,3,4,5,6,7,8,9,10,11,12
2           2,3
3           3,7,8,9
4           2,6
5           4,5
（所影响的行数为 5 行）
*/

Answer 2

--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据：[TB]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') 
AND type in (N'U')) 
DROP TABLE [TB]
GO---->建表
create table [TB]([COL1] varchar(3),[COL2] varchar(18))
insert [TB]
select 'ABC','123 4568 1234565' union all
select 'DBD','1231 56455 5645665'
GO--> 查询结果
SELECT * FROM [TB]SELECT 
a.[COL1],
col=SUBSTRING(a.[COL2],number,CHARINDEX(' ',a.[COL2]+' ',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.[COL2],number)=number
--> 删除表格
--DROP TABLE [TB]

Answer 3

--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据：[TB]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') 
AND type in (N'U')) 
DROP TABLE [TB]
GO---->建表
create table [TB]([COL1] varchar(3),[COL2] varchar(18))
insert [TB]
select 'ABC','123 4568 1234565' union all
select 'DBD','1231 56455 5645665'
GO--> 查询结果
SELECT * FROM [TB]SELECT 
a.[COL1],
col=SUBSTRING(a.[COL2],number,CHARINDEX(' ',a.[COL2]+' ',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.[COL2],number)=number
--> 删除表格
--DROP TABLE [TB]--
COL1 col
ABC 123
ABC 4568
ABC 1234565
DBD 1231
DBD 56455
DBD 5645665

Answer 4

if object_id('[TB]') is not null drop table [User]
go
create table [TB]([COL1] varchar(3),[COL2] varchar(18)) 
 insert [TB] 
 select 'ABC','123 4568 1234565' union all 
 select 'DBD','1231 56455 5645665' 
 GO select B.[COL1],substring(B.[COL2],p.number,charindex(' ',B.[COL2]+' ',p.number)-p.number) [COL2]
from TB B
join master..spt_values p
on p.type='P'
and charindex(' ',' '+B.[COL2],number)=number COL1 COL2
---- ------------------
ABC  123
ABC  4568
ABC  1234565
DBD  1231
DBD  56455
DBD  5645665(6 行受影响)

Answer 5

if object_id('[TB]') is not null drop table [User]
go
create table [TB]([COL1] varchar(3),[COL2] varchar(18)) 
 insert [TB] 
 select 'ABC','123 4568 1234565' union all 
 select 'DBD','1231 56455 5645665' 
 GO select B.[COL1],substring(B.[COL2],p.number,charindex(' ',B.[COL2]+' ',p.number)-p.number) [COL2]
from TB B
join master..spt_values p
on p.type='P'
and charindex(' ',' '+B.[COL2],number)=number COL1 COL2
---- ------------------
ABC  123
ABC  4568
ABC  1234565
DBD  1231
DBD  56455
DBD  5645665(6 行受影响)

Answer 6

6楼这位大哥,
join master..spt_values p
on p.type='P'这是什么意思

Answer 7

master..spt_values这是一张系统表
你试试:select * from master..spt_values where type='p'就知道了

Answer 8

系统函数你运行这个select number from master..spt_values where type='p'

Answer 9

if object_id('[TB]') is not null drop table [TB]
 go
 create table [TB]([COL1] varchar(3),[COL2] varchar(18)) 
  insert [TB] 
  select 'ABC','123,4568,1234565' union all 
  select 'DBD','1231,56455,5645665' 
  GO 
 
 select B.[COL1],substring(B.[COL2],p.number,charindex(',',B.[COL2]+',',p.number)-p.number) [COL2]
 from TB B
 join master..spt_values p
 on p.type='P'
 and charindex(',',','+B.[COL2],number)=number 
 
COL1 COL2
---- ------------------
ABC  123
ABC  4568
ABC  1234565
DBD  1231
DBD  56455
DBD  5645665(6 行受影响)