SQL字符串拆分问题

hexl0325 2010-09-26 12:07:16
有如下表,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

不知怎么搞,请教高手
...全文
185 点赞 收藏 12
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
ws_hgo 2010-09-28
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 行受影响)
回复
喜-喜 2010-09-26
[Quote=引用 4 楼 wxf163 的回复:]
SQL code

--> 数据库版本:
--> 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 [……
[/Quote]

up..

学习..
回复
ws_hgo 2010-09-26
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 行受影响)
回复
ws_hgo 2010-09-26
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 行受影响)
回复
王向飞 2010-09-26

--> 数据库版本:
--> 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

回复
王向飞 2010-09-26

--> 数据库版本:
--> 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]

回复
ws_hgo 2010-09-26
/*
标题:分解字符串并查询相关数据
作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间: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 + ',') > 0

drop 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 行)
*/

回复
王向飞 2010-09-26
又来空格,真是无穷无尽的思想,无穷无尽的需求。。
回复
ws_hgo 2010-09-26
兄弟问题解决啦
就结贴啊
回复
ws_hgo 2010-09-26
[Quote=引用 8 楼 hexl0325 的回复:]

6楼这位大哥,
join master..spt_values p
on p.type='P'

这是什么意思
[/Quote]

系统函数

你运行这个

select number from master..spt_values where type='p'
回复
zsh0809 2010-09-26
[Quote=引用 8 楼 hexl0325 的回复:]

6楼这位大哥,
join master..spt_values p
on p.type='P'

这是什么意思
[/Quote]
master..spt_values这是一张系统表
你试试:select * from master..spt_values where type='p'就知道了
回复
hexl0325 2010-09-26
6楼这位大哥,
join master..spt_values p
on p.type='P'

这是什么意思
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-09-26 12:07
社区公告
暂无公告