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

不知怎么搞,请教高手
...全文
206 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
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'
「已注销」 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'

这是什么意思
sqlserver 字符串分割、包含、匹配
2、按指定符号分割字符串，返回“分割后指定索引的第几个元素”的值，象数组一样方便 3、检查一个元素是否在数组中，返回1或0 4、检查一个元素是否与数组中的相匹配，并返回相匹配的数组的值。参数值为0表示全模糊...
SQL字符串分割
在查询表时需要对单个字段分割成多个字段非常有用！
字符串分割的字符串数量 SQL
用 逗号等分隔符 分开的字符串数量 比如 aaa,bbb,ccc 输出3
SQL字符串拆分函数
SQL字符串拆分函数，可拆分任何格式的字符串，只需传入待拆分字符串及拆分参数即可
sql字符串分割
sql字符串分割,sqlserver 表值函数， 对包含逗号的字符串进行分割并返回表。
疑难问题

22,207

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章