————————-SQL SERVER函數將人民幣數字轉換成大寫形式——R

CREATE  FUNCTION [dbo].[f_num_chn] (@num numeric(14,5))  
RETURNS varchar(100) WITH ENCRYPTION  
AS  
BEGIN  
--著作權：weihuachao@sina.com  
  DECLARE @n_data VARCHAR(20),@c_data VARCHAR(100),@n_str VARCHAR(10),@i int  
  SET @n_data=RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100) AS bigint) AS varchar(20)),14)  
  SET @c_data=''  
  SET @i=1  
  WHILE @i<=14  
  BEGIN  
    SET @n_str=SUBSTRING(@n_data,@i,1)  
    IF @n_str<>' '  
    BEGIN  
      IF not ((SUBSTRING(@n_data,@i,2)='00') or  
        ((@n_str='0') and ((@i=4) or (@i=8) or (@i=12) or (@i=14))))  
        SET @c_data=@c_data+SUBSTRING('零壹貳三肆伍陸柒捌玖',CAST(@n_str AS int)+1,1)  
      IF not ((@n_str='0') and (@i<>4) and (@i<>8) and (@i<>12))  
        SET @c_data=@c_data+SUBSTRING('仟佰拾億仟佰拾萬仟佰拾圓角分',@i,1)  
      IF SUBSTRING(@c_data,LEN(@c_data)-1,2)='億萬'  
        SET @c_data=SUBSTRING(@c_data,1,LEN(@c_data)-1)  
    END  
    SET @i=@i+1  
  END  
  IF @num<0  
    SET @c_data='（負數）'+@c_data  
  IF @num=0  
    SET @c_data='零圓'  
  IF @n_str='0'  
    SET @c_data=@c_data+'整'  
  RETURN(@c_data)  
END  

 

 SQL SERVER函數將人民幣數字轉換成大寫形式
閑著也是閑著，歡迎大家多提寶貴意見

--阿拉伯數字轉中文大寫數字
Create   function NToCC(@n int)
returns varchar(2)
as
begin

declare @s varchar(2)

if @n=0
set @s=''零''
if @n=1
set @s=''壹''
if @n=2
set @s=''貳''
if @n=3
set @s=''三''
if @n=4
set @s=''肆''
if @n=5
set @s=''伍''
if @n=6
set @s=''陸''
if @n=7
set @s=''柒''
if @n=8
set @s=''捌''
if @n=9
set @s=''玖''

return @s

end

---

--可支援到上億
Create   function numbertoChineseBigString(@m money)
returns varchar(255)
begin

declare @i varchar(255)
declare @f varchar(255)
declare @r varchar(255)
declare @s varchar(255)

set @s=convert(varchar(255),@m)
if charindex(''.'',@s)>0
begin
    set @i=substring(@s,1,charindex(''.'',@s)-1)
    set @f=substring(@s,charindex(''.'',@s)+1,len(@s))
end
else
set @i=@s

set @r=''''

declare @k int
set @k=len(@i)
while @k>=1
begin

--最多9位
if @k=9
set @r=@r+
dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))
--@i
+''億''

if @k=8
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''千''

if @k=7
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''佰''

if @k=6
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''拾''

if @k=5
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''萬''

if @k=4
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''千''

if @k=3
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''佰''

if @k=2
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''拾''

if @k=1
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''元''

set @k=@k-1

end

 

if convert(float,@f)=0
set @r=@r+''整''
else
begin

if substring(@s,1,1)>''''
set @r=@r+dbo.NToCC(convert(int,substring(@f,1,1)))+''角''

if substring(@s,2,1)>''''
set @r=@r+dbo.NToCC(convert(int,substring(@f,2,1)))+''分''

end

set @r=replace(@r,''零千零佰零拾'',''零'')
set @r=replace(@r,''零佰零拾'',''零'')
set @r=replace(@r,''零千零佰'',''零'')
set @r=replace(@r,''零拾'',''零'')
set @r=replace(@r,''零千'',''零'')
set @r=replace(@r,''零佰'',''零'')
set @r=replace(@r,''拾零萬'',''拾萬'')
return @r

end

--     select dbo.numbertoChineseBigString(100102)

文章出處：http://www.diybl.com/course/7_databases/sql/sqlServer/2008224/101116.html

 

CREATE FUNCTION dbo.L2U(@n_LowerMoney numeric(15,2),@v_TransType int)

RETURNS VARCHAR(200) AS

BEGIN

Declare @v_LowerStr VARCHAR(200) -- 小寫金額

Declare @v_UpperPart VARCHAR(200)

Declare @v_UpperStr VARCHAR(200) -- 大寫金額

Declare @i_I int

set @v_LowerStr = LTRIM(RTRIM(ROUND(@n_LowerMoney,2))) --四捨五入為指定的精度並刪除資料左右空格

set @i_I = 1

set @v_UpperStr = ''

while ( @i_I <= len(@v_LowerStr))

begin

select @v_UpperPart = case substring(@v_LowerStr,len(@v_LowerStr) - @i_I + 1,1)

WHEN '.' THEN '元'

WHEN '0' THEN '零'

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 '玖'

END

+

case @i_I

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 10 THEN '佰'

WHEN 11 THEN '仟'

WHEN 12 THEN '億'

WHEN 13 THEN '拾'

WHEN 14 THEN '佰'

WHEN 15 THEN '仟'

WHEN 16 THEN '萬'

ELSE ''

END

set @v_UpperStr = @v_UpperPart + @v_UpperStr

set @i_I = @i_I + 1

end

if ( 0 = @v_TransType)

begin

set @v_UpperStr = REPLACE(@v_UpperStr,'零拾','零')

set @v_UpperStr = REPLACE(@v_UpperStr,'零佰','零')

set @v_UpperStr = REPLACE(@v_UpperStr,'零仟','零')

set @v_UpperStr = REPLACE(@v_UpperStr,'零零零','零')

set @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')

set @v_UpperStr = REPLACE(@v_UpperStr,'零角零分','整')

set @v_UpperStr = REPLACE(@v_UpperStr,'零分','整')

set @v_UpperStr = REPLACE(@v_UpperStr,'零角','零')

set @v_UpperStr = REPLACE(@v_UpperStr,'零億零萬零元','億元')

set @v_UpperStr = REPLACE(@v_UpperStr,'億零萬零元','億元')

set @v_UpperStr = REPLACE(@v_UpperStr,'零億零萬','億')

set @v_UpperStr = REPLACE(@v_UpperStr,'零萬零元','萬元')

set @v_UpperStr = REPLACE(@v_UpperStr,'萬零元','萬元')

set @v_UpperStr = REPLACE(@v_UpperStr,'零億','億')

set @v_UpperStr = REPLACE(@v_UpperStr,'零萬','萬')

set @v_UpperStr = REPLACE(@v_UpperStr,'零元','元')

set @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')

end

-- 對壹元以下的金額的處理

if ( '元' = substring(@v_UpperStr,1,1))

begin

set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))

end

if ( '零' = substring(@v_UpperStr,1,1))

begin

set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))

end

if ( '角' = substring(@v_UpperStr,1,1))

begin

set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))

end

if ( '分' = substring(@v_UpperStr,1,1))

begin

set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))

end

if ('整' = substring(@v_UpperStr,1,1))

begin

set @v_UpperStr = '零元整'

end

return @v_UpperStr

END