create table #a(一字段 varchar(100)) insert #a values(100) insert #a values(10001) insert #a values(10002) insert #a values(101) insert #a values(10101) insert #a values(1010101) insert #a values(1010102)SELECT case len(一字段) when 5 then STUFF(一字段,4,0, '/') when 7 then STUFF(STUFF(一字段,4,0, '/'),7,0, '/') else 一字段 end 一字段 from #a go drop table #a
1: Create function test(@a varchar(200)) RETURNS varchar(200) as begin declare @b varchar(20),@i int if len(@a) > 3 begin set @b = left(@a,3) set @a = right(@a,len(@a)-3) while len(@a)>2 begin set @b = @b + '/'+ left(@a,2) set @a = right(@a,len(@a)-2) end if len(@a) > 0 set @b = @b + '/' + @a end else set @b = @a Return(@b) end 2: Select dbo.test(字段) from 表
create table #a(i varchar(1000)) insert into #a select '100' insert into #a select '10001' insert into #a select '10002' insert into #a select '101' insert into #a select '10101' insert into #a select '1010101' insert into #a select '1010102' go select * from #a go select * from #a where len(i)<=3 union all select left(i,3)+'/'+right(i,2) from #a where len(i) between 4 and 5 union all select left(i,3)+'/'+substring(i,6,2)+'/'+ right(i,2) from #a where len(i) between 6 and 7 order by i
insert #a values(100)
insert #a values(10001)
insert #a values(10002)
insert #a values(101)
insert #a values(10101)
insert #a values(1010101)
insert #a values(1010102)SELECT case len(一字段) when 5 then STUFF(一字段,4,0, '/') when 7 then STUFF(STUFF(一字段,4,0, '/'),7,0, '/') else 一字段 end 一字段 from #a
go
drop table #a
Create function test(@a varchar(200))
RETURNS varchar(200)
as
begin
declare @b varchar(20),@i int
if len(@a) > 3
begin
set @b = left(@a,3)
set @a = right(@a,len(@a)-3)
while len(@a)>2
begin
set @b = @b + '/'+ left(@a,2)
set @a = right(@a,len(@a)-2)
end
if len(@a) > 0
set @b = @b + '/' + @a
end
else
set @b = @a
Return(@b)
end
2:
Select dbo.test(字段) from 表
123/4 123/45 123/45/6 123/45/67(所影响的行数为 1 行)
insert into #a select '100'
insert into #a select '10001'
insert into #a select '10002'
insert into #a select '101'
insert into #a select '10101'
insert into #a select '1010101'
insert into #a select '1010102'
go
select * from #a
go
select * from #a where len(i)<=3
union all
select left(i,3)+'/'+right(i,2) from #a where len(i) between 4 and 5
union all
select left(i,3)+'/'+substring(i,6,2)+'/'+ right(i,2) from #a where len(i) between 6 and 7
order by i