--//--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
解决方案 »
- 时间类型转换问题
- 求解一个方案
- CONVERT 小问题.
- (初学)最近一周生日查询语句实现问题!
- 求解:SQL server 2000
- 求助,紧急求助!!!100分,先到先得
- 请问关于在sql中如何实现类似于两层循环的查询,先查出数据集1,然后根据数据集1的结果的每一项来进行查询,得到最终的结果?
- 关于数据库日志的错误
- SQL Server上有 IIf 函数说明,为什么不能用啊!谢谢
- 紧急求救:数据库只读
- 如何编写存储过程来进行用户登陆验证及用户操作验证
- 自增长子段的问题:假如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