表 EMAIL 字段 RECEIVER ,RECEIVER 字段里有多个数据,想把这些数据分开显示,如下:当前字段内容如下:
RECEIVER
10,20,11
33
22,10
24
14,11需要实现的结果:
RECEIVER
10
20
11
33
22
10
24
14
11有大大能指导一下吗?
RECEIVER
10,20,11
33
22,10
24
14,11需要实现的结果:
RECEIVER
10
20
11
33
22
10
24
14
11有大大能指导一下吗?
解决方案 »
- SQL SERVER2005如何将如一内容例“123456789”,在指定数据库中查找相关表
- 如何取最后两个字符之间的值
- (50分)求存储过程:问题比较长,请耐心看一下!!!
- 怎么写这个SQL语句?
- 请教如何将access的数据库导入到sqlserver中?
- 请教tempdb的问题
- 对于没有备份数SQL数据库如何进行恢复??
- 怎样制作ASP+SQL SERVER 的打包程序
- 如何实现数据库的导入、导出?????
- 在查询时 如何把一个时间字段的查询结果格式化为如:“2015-03-14 08:05:02”;而不是默认的“2015-03-14 08:05:02” ;相关的s
- 截取字符串时,查找某字符第二次出现的位置,怎么查啊
- 触发器不起作用,用来更新字段的
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([RECEIVER] [nvarchar](10))
INSERT INTO [tb]
SELECT '10,20,11' UNION ALL
SELECT '33' UNION ALL
SELECT '22,10' UNION ALL
SELECT '24' UNION ALL
SELECT '14,11'--SELECT * FROM [tb]-->SQL查询如下:
SELECT b.[RECEIVER]
FROM (
SELECT [RECEIVER]=CAST('<v>'+REPLACE([RECEIVER],',','</v><v>')+'</v>' AS XML)
FROM tb
) AS a
OUTER APPLY (
SELECT C.value('.','varchar(50)') AS [RECEIVER]
FROM a.[RECEIVER].nodes('/v') AS T(C)
) AS b
/*
RECEIVER
--------------------------------------------------
10
20
11
33
22
10
24
14
11(9 行受影响)
*/
create table tb(col varchar(10))
insert into tb
select 'aa,bb,cc' union all
select 'aa,bb' union all
select 'aa'select
a.col
,[splt_char] = substring(a.col,b.number,charindex(',',a.col+',',b.number)-b.number)
from tb a
inner join master..spt_values b
on charindex(',',','+a.col,b.number) = b.number
and b.[type] = 'P'--结果
/*
aa,bb,cc aa
aa,bb,cc bb
aa,bb,cc cc
aa,bb aa
aa,bb bb
aa aa
*/
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([RECEIVER] [nvarchar](100))
INSERT INTO [tb]
SELECT '10,20,11' UNION ALL
SELECT '33' UNION ALL
SELECT '22,10' UNION ALL
SELECT '24' UNION ALL
SELECT '14,11'
go
Select
COl=substring(A.[RECEIVER],b.number,charindex(',',A.[RECEIVER]+',',b.number)-b.number)
from
Tb A join master..spt_values B
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.[RECEIVER])
where
substring(','+A.[RECEIVER],B.number,1)=','
/*
COl2
----------------------------------------------------------------------------------------------------
10
20
11
33
22
10
24
14
11*/