@str='1,2,3,4,5,6,7,8,9,0' ,为分隔符早SQL中如何分割字符串,并用个临时表装起来断电啦,明天早上来结
解决方案 »
- 中控二次开发 zkemkeeper ref out
- C# 实现图片浏览功能!
- <怎么打WINFORM中实现 点击按钮 弹出网页 来显示我TEXTBOX的HTML>
- 请大家帮帮忙,remoting 服务器半闭重起后客户怎么办?重新注册时出错了。急急急急急急急急急!!!!!!!!!!!!!!!!!!!
- nhibernate中如何时间将一个子查询作为查询结果,类似下面的sql语句
- 关于treeview的更新问题
- 谁有研究过用C#怎么实现断点续传和断点下载??
- 诚心请教!!!
- 怎样判断网上的一文件是否存在?有网络编程经验的来
- C# winform 导出Excel问题
- 鼠标拖拽效果
- 选择哪个软件开发工具更合适呢?
@sInputList VARCHAR(8000), -- List of delimited items
@sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
)
RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0 INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
ENDGO--Testselect * from fnSplit('1,2,3,4,5,6,7,8,9,0', ',')
a.id,
col=SUBSTRING(a.col,number,CHARINDEX(',',a.col+',',number)-b.number)
FROM tb a
JOIN master..spt_values b
ON b.type='p'
AND CHARINDEX(',',','+a.col,number)=number
2005
SELECT a.id,b.col
FROM (SELECT id,col=CAST('<v>'+REPLACE(col,',','</v><v>')+'</v>' AS xml) FROM tb) a
OUTER APPLY (SELECT col=T.C.value('.','varchar(50)') FROM a.col.nodes('/v') AS T(C)) b
set @str='1,2,3,4,5,6,7,8,9,0'
set @str = replace(@str, ',',' as a union select ')
execute sp_executesql @s '返回一个数据集。
create function Split (
@StringToSplit varchar(2048),
@Separator varchar(128))
returns table as return
with indices as
(
select 0 S, 1 E
union all
select E, charindex(@Separator, @StringToSplit, E) + len(@Separator)
from indices
where E > S
)
select substring(@StringToSplit,S,
case when E > len(@Separator) then e-s-len(@Separator) else len(@StringToSplit) - s + 1 end) String
,S StartIndex
from indices where S >0
select String from Split('1,2,3,4,5,6,7,8,9,0' , ',') group by string /*
String
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0
1
2
3
4
5
6
7
8
9(10 row(s) affected)
*/