create table nian (n varchar (4))
insert into nian
select '2004' union
select '1981' union
select '3045'
select (case convert (varchar (1),n)
when 1 then '一'
when 2 then '二'
when 3 then '三'
when 4 then '四'
when 5 then '五'
when 6 then '六'
when 7 then '七'
when 8 then '八'
when 9 then '九'
when 0 then '零'end)+
(case substring(n,2,1)
when 1 then '一'
when 2 then '二'
when 3 then '三'
when 4 then '四'
when 5 then '五'
when 6 then '六'
when 7 then '七'
when 8 then '八'
when 9 then '九'
when 0 then '零'end)+
(case substring(n,3,1)
when 1 then '一'
when 2 then '二'
when 3 then '三'
when 4 then '四'
when 5 then '五'
when 6 then '六'
when 7 then '七'
when 8 then '八'
when 9 then '九'
when 0 then '零'end)+
(case substring(n,3,1)
when 1 then '一'
when 2 then '二'
when 3 then '三'
when 4 then '四'
when 5 then '五'
when 6 then '六'
when 7 then '七'
when 8 then '八'
when 9 then '九'
when 0 then '零'end)
from nian
insert into nian
select '2004' union
select '1981' union
select '3045'
select (case convert (varchar (1),n)
when 1 then '一'
when 2 then '二'
when 3 then '三'
when 4 then '四'
when 5 then '五'
when 6 then '六'
when 7 then '七'
when 8 then '八'
when 9 then '九'
when 0 then '零'end)+
(case substring(n,2,1)
when 1 then '一'
when 2 then '二'
when 3 then '三'
when 4 then '四'
when 5 then '五'
when 6 then '六'
when 7 then '七'
when 8 then '八'
when 9 then '九'
when 0 then '零'end)+
(case substring(n,3,1)
when 1 then '一'
when 2 then '二'
when 3 then '三'
when 4 then '四'
when 5 then '五'
when 6 then '六'
when 7 then '七'
when 8 then '八'
when 9 then '九'
when 0 then '零'end)+
(case substring(n,3,1)
when 1 then '一'
when 2 then '二'
when 3 then '三'
when 4 then '四'
when 5 then '五'
when 6 then '六'
when 7 then '七'
when 8 then '八'
when 9 then '九'
when 0 then '零'end)
from nian
insert into nian
select '2004' union
select '1981' union
select '3045'
select (case convert (varchar (1),n)
when 1 then '一'
when 2 then '二'
when 3 then '三'
when 4 then '四'
when 5 then '五'
when 6 then '六'
when 7 then '七'
when 8 then '八'
when 9 then '九'
when 0 then '零'end)+
(case substring(n,2,1)
when 1 then '一'
when 2 then '二'
when 3 then '三'
when 4 then '四'
when 5 then '五'
when 6 then '六'
when 7 then '七'
when 8 then '八'
when 9 then '九'
when 0 then '零'end)+
(case substring(n,3,1)
when 1 then '一'
when 2 then '二'
when 3 then '三'
when 4 then '四'
when 5 then '五'
when 6 then '六'
when 7 then '七'
when 8 then '八'
when 9 then '九'
when 0 then '零'end)+
(case substring(n,4,1)
when 1 then '一'
when 2 then '二'
when 3 then '三'
when 4 then '四'
when 5 then '五'
when 6 then '六'
when 7 then '七'
when 8 then '八'
when 9 then '九'
when 0 then '零'end)
from nian
CREATE FUNCTION [dbo].[f_num_str] (@num int)
RETURNS varchar(100)
AS
BEGIN
DECLARE @n_str VARCHAR(20),@re VARCHAR(20),@i int
SELECT @n_str=cast(@num as varchar),@i=1,@re=''
WHILE @i<=len(@n_str)
BEGIN
SET @re=@re+SUBSTRING('零壹贰叁肆伍陆柒捌玖',CAST(SUBSTRING(@n_str,@i,1) AS int)+1,1)
SET @i=@i+1
END
RETURN @re
END
我以为有个set函数可以解决这个问题,难道没有?
returns varchar(1000)
as
begin
declare @s varchar(100),@i int
set @s=cast(@a as varchar)
declare @v varchar(10)
set @i=0
while @i<=9
begin
set @v=''
set @v=(case when @i=0 then '零'
when @i=1 then'壹'
when @i=2 then '貳'
when @i=3 then '參'
when @i=4 then '肆'
when @i=5 then '伍'
when @i=6 then '陸'
when @i=7 then '蕖'
when @i=8 then '捌'
when @i=9 then '玖' end)
set @s=replace(@s,cast(@i as varchar),@v)
set @i=@i+1
end
return (@s)
enddrop function dbo.fn_dselect dbo.fn_d(2004)
--結果
貳零零肆select dbo.fn_d(2000433)
--結果
貳零零零肆參參
returns varchar(1000)
as
begin
declare @s varchar(100),@i int
set @s=cast(@a as varchar)
declare @v varchar(10)
set @i=0
while @i<=9
begin
set @v=''
set @v=(case when @i=0 then '零'
when @i=1 then'一'
when @i=2 then '二'
when @i=3 then '三'
when @i=4 then '四'
when @i=5 then '五'
when @i=6 then '六'
when @i=7 then '七'
when @i=8 then '八'
when @i=9 then '九' end)
set @s=replace(@s,cast(@i as varchar),@v)
set @i=@i+1
end
return (@s)
enddrop function dbo.fn_dselect dbo.fn_d(2004)
--結果
二零零四select dbo.fn_d(2000433)
--結果
二零零零四三三
create function dbo.fn_d(@a varchar(1000))
returns varchar(1000)
as
begin
declare @s varchar(100),@i int
declare @v varchar(10),@re varchar(1000)
select @s='',@v='',@re=''
set @i=1
while @i<=len(@a)
begin
set @v=substring(@a,@i,1)
set @s=(case when @v='0' then '零'
when @v='1' then'一'
when @v='2' then '二'
when @v='3' then '三'
when @v='4' then '四'
when @v='5' then '五'
when @v='6' then '六'
when @v='7' then '七'
when @v='8' then '八'
when @v='9' then '九' else '-' end)
--set @a=replace(@a,@v,@s)
set @re=@re+@s
set @i=@i+1
end
return (@re)
enddrop function dbo.fn_dselect dbo.fn_d('2004-09-21')
--結果
二零零四-零九-二一