--//--Split的函数,邹老大写的---create function f_splitStr
(
@s varchar(8000), --待分拆的字符串
@split varchar(100)--分隔符
)
returns @re table(col varchar(100))
as
begin
-----循环使用Left和Charindex
insert @re values(@s)
return
end
(
@s varchar(8000), --待分拆的字符串
@split varchar(100)--分隔符
)
returns @re table(col varchar(100))
as
begin
-----循环使用Left和Charindex
insert @re values(@s)
return
end
解决方案 »
- 求教:少量數據SQL slecte查詢很慢的問題
- 查询哪个系统表可以获得表的描述信息和字段的描述信息?
- 这样的sql语句该怎么写?有图,请进
- 请问事件探察器的作用是什么?一般怎样使用?
- 请教SQL 2005 Report Server的报表如何取当前用户信息?
- 下午茶时间求两表并列前三名总分SQL2008语句
- 存储过程改错!!
- 请教高手:怎样在ASP中获得存储过程执行的结果?
- 请大家推荐PB和DBMS的英文网站
- eclipse里的getString函数的参数是什么意思
- 如何编写存储过程来进行用户登陆验证及用户操作验证
- 自增长子段的问题:假如ID的字段值有1,100,101,102,... ,我现在想增加一条ID为2的记录,同时要求该字段执行该操作后仍然是自增长型的,如
select 2 as order,7 as key unill all
select 3 as order,8 as key unill all
select 4 as order,1 as key unill all用Order字段来排序,关联用 Key字段最后还要写一个函数 吧记录还原成字符串!!
---------------感觉是不是太麻烦了!!
[create] table t
(col varchar(2000),col1 int)insert t
select '1,3,7,7,3,3,6,6,9,9',1 [union] all
select '1,3,5,6,8',2 [union] all
select '4,3,3,3,4,5,6',3
goselect col1,col from tselect top 100 identity(int,1,1) as id into # from sysobjects a,sysobjects bselect col1,col into #1 from (
select col1,col=substring(a.col,[id],charindex(',',a.col+',',[id])-b.id)
from t a,# b
where b.[id]<=len(a.col)
and charindex(',',','+a.col,id)=id) a
where col in (3,7,8,1)
order by col1,charindex(col,'3,7,8,1')declare @col1 varchar(10),@col2 varchar(100)
update #1 set
@col2=case when @col1=col1 then @col2+','+col else col end,
@col1=col1,
col=@col2
select a.col1,a.col+b.col as col from
(select col1,col=max(col) from #1 group by col1) a
inner join
(select col1,col=','+replace(replace(replace(replace(col,'3,',''),'7,',''),'8,',''),'1,','') from t) b
on a.col1=b.col1
drop table #1
drop table #
drop table t
col1 col
1 1,3,7,7,3,3,6,6,9,9
2 1,3,5,6,8
3 4,3,3,3,4,5,6结果是对的
col1 col
1 3,3,3,7,7,1,6,6,9,9
2 3,8,1,5,6,8
3 3,3,3,4,4,5,6但是如果输入的是
col1 col
1 1,3,7,7,3,3,6,6,9,9
2 1,3,5,6,8
3 4,3,3,23,4,5,6结果却是,23不能通过。
col1 col
1 3,3,3,7,7,1,6,6,9,9
2 3,8,1,5,6,8
3 3,3,4,24,5,6
insert t select '1,3,7,7,3,3,6,6,9,9',1
insert t select '1,3,5,6,8',2
insert t select '4,3,3,23,4,5,6',3
go
create function f_str(@str varchar(1000))
returns varchar(1000)
as
begin
declare @t table(id int)
while charindex(',',@str)>0
begin
insert into @t select left(@str,charindex(',',@str)-1)
set @str = stuff(@str,1,charindex(',',@str),'')
end
insert into @t select @str
set @str = ''
select
@str = @str+','+rtrim(a.id)
from
(select
top 100 percent id
from
@t
order by
case id
when 3 then 1
when 7 then 2
when 8 then 3
when 1 then 4
else 5
end,id) a
set @str = stuff(@str,1,1,'')
return @str
end
goselect dbo.f_str(col) from t
/*
3,3,3,7,7,1,6,6,9,9
3,8,1,5,6
3,3,4,4,5,6,23
*/drop function f_str
drop table t