SQL 规则查询 #################################### 100%结贴
求一函数，实现如下功能:
示例
输入：“cccddecc” 输出：“3c2de2c”
输入：“adef” 输出：“adef”
输入：“pppppppp” 输出：“8p”
[解决办法]
试试这个：
create function dbo.calc_str(@str varchar(1000))
returns varchar(1000)
as
begin
declare @v varchar(10)
declare @prev varchar(10)
declare @i int
declare @str_v varchar(1000)
declare cur cursor
for select SUBSTRING(v,s.number,1) v
from (select @str as v)t,master..spt_values s
where s.number> 0 and s.type = 'P'
and s.number <= LEN(@str)
set @i = 0
set @str_v = ''
open cur
fetch next from cur into @v
set @prev = @v
while @@FETCH_STATUS = 0
begin
if @v = @prev
begin
set @i = @i + 1
end
else
begin
Set @str_v = @str_v + case when @i = 1 then @prev
else CAST(@i as varchar)+@prev
end
set @i = 1
end
set @prev = @v
fetch next from cur into @v
end
Set @str_v = @str_v + case when @i = 1 then @prev
else CAST(@i as varchar)+@prev
end
close cur
deallocate cur
return @str_v
end
go
select dbo.calc_str(v)
from
(
select 'cccddecc' v union all
select 'adef' union all
select 'pppppppp'
)t
/*
3c2de2c
adef
8p
*/
create function dbo.fndfbb
(@x varchar(50)) returns varchar(50)
as
begin
declare @y varchar(50)
;with t as
(select b.number 'n',
substring(a.s,b.number,1) 'x'
from (select @x 's')a,master.dbo.spt_values b
where b.type='P' and b.number between 1 and len(a.s)
),
u as
(select a.n,a.x,row_number() over(order by a.n) 'r'
from t a
left join t b on a.n=b.n-1
where a.x<>b.x or b.n is null)
select @y=isnull(@y,'')
+case when rtrim(a.n-isnull(b.n,0))<>'1' then rtrim(a.n-isnull(b.n,0))
else '' end+a.x
from u a
left join u b on a.r=b.r+1
order by a.r
return @y
end
-- 输入：“cccddecc”
select dbo.fndfbb('cccddecc') 'output'
/*
output
--------------------------------------------------
3c2de2c
(1 row(s) affected)
*/
-- 输入：“adef”
select dbo.fndfbb('adef') 'output'
/*
output
--------------------------------------------------
adef
(1 row(s) affected)
*/
-- 输入：“pppppppp”
select dbo.fndfbb('pppppppp') 'output'
/*
output
--------------------------------------------------
8p
(1 row(s) affected)
*/
