数字大小写转换（包括金额）

最新推荐文章于 2021-03-11 03:15:25 发布

focuswe

最新推荐文章于 2021-03-11 03:15:25 发布

阅读量5.2k

点赞数

分类专栏： SQL 文章标签： string function

本文链接：https://blog.csdn.net/focuswe/article/details/3208515

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

（原创）自己写了一个数字转换成大小写，有8种格式，大家可以相互转载，请注明出处。

/*
传入一组数，返回其大写格式
@Num  数
@FormatType 格式（【数字大写】100:普通格式 101:[数量]单位格式 102:只处理整数部份 103:金额加单位格式
   【中文大写】200:普通格式 201:[数量]单位格式 202:只处理整数部份 203:金额加单位格式
   ）
*/
CREATE FUNCTION GetNumberFormat(@Num numeric(18,4),@FormatType INT)
RETURNS NVARCHAR(100)

AS

BEGIN
DECLARE @RE   NVARCHAR(100)  --返回值
DECLARE @FormatNum NVARCHAR(100)  --需要格式化的数据
DECLARE @DecimalPlace NVARCHAR(100)  --需要格式化的数据的小数位
DECLARE @LenNum  INT   --长度
DECLARE @String1 NVARCHAR(50)  --单位1
DECLARE @String2 NVARCHAR(50)  --单位2
DECLARE @String3 NVARCHAR(50)  --单位3

SELECT @DecimalPlace = N''
SELECT @String1 = N'元角分厘'
SELECT @String2 = N'十百千万十百千亿十百千万十百千'
SELECT @String3 = N'拾佰仟万拾佰仟亿拾佰仟万拾佰仟'

SELECT @FormatNum=CONVERT(NVARCHAR(100),@Num)
SELECT @RE=N''
SELECT @LenNum=LEN(@Num)

IF @FormatType = 100 OR @FormatType = 101 OR @FormatType = 102 OR @FormatType = 103
BEGIN
SELECT @FormatNum = Replace(@FormatNum,N'0',N'零')
SELECT @FormatNum = Replace(@FormatNum,N'1',N'一')
SELECT @FormatNum = Replace(@FormatNum,N'2',N'二')
SELECT @FormatNum = Replace(@FormatNum,N'3',N'三')
SELECT @FormatNum = Replace(@FormatNum,N'4',N'四')
SELECT @FormatNum = Replace(@FormatNum,N'5',N'五')
SELECT @FormatNum = Replace(@FormatNum,N'6',N'六')
SELECT @FormatNum = Replace(@FormatNum,N'7',N'七')
SELECT @FormatNum = Replace(@FormatNum,N'8',N'八')
SELECT @FormatNum = Replace(@FormatNum,N'9',N'九')
END
ELSE IF @FormatType = 200 OR @FormatType = 201 OR @FormatType = 202 OR @FormatType = 203
BEGIN
SELECT @FormatNum = Replace(@FormatNum,N'0',N'零')
SELECT @FormatNum = Replace(@FormatNum,N'1',N'壹')
SELECT @FormatNum = Replace(@FormatNum,N'2',N'贰')
SELECT @FormatNum = Replace(@FormatNum,N'3',N'叁')
SELECT @FormatNum = Replace(@FormatNum,N'4',N'肆')
SELECT @FormatNum = Replace(@FormatNum,N'5',N'伍')
SELECT @FormatNum = Replace(@FormatNum,N'6',N'陆')
SELECT @FormatNum = Replace(@FormatNum,N'7',N'柒')
SELECT @FormatNum = Replace(@FormatNum,N'8',N'捌')
SELECT @FormatNum = Replace(@FormatNum,N'9',N'玖')
END

IF @LenNum=0
BEGIN
SET @RE = N''
END
ELSE
BEGIN
IF @FormatType=100 OR @FormatType=200
BEGIN
  SET @RE = @FormatNum
END
ELSE IF @FormatType=101 OR @FormatType=201 OR @FormatType = 103 OR @FormatType=102 OR @FormatType=202 OR @FormatType = 203
BEGIN
  IF CHARINDEX('.',@FormatNum)!=0
  BEGIN
   --小数位
   SET @DecimalPlace = RIGHT(@FormatNum,@LenNum-CHARINDEX('.',@FormatNum))
   --整数位
   SET @FormatNum = LEFT(@FormatNum,CHARINDEX('.',@FormatNum)-1)
   --重置整数长度
   SELECT @LenNum=LEN(@FormatNum)
  END

  WHILE (@LenNum >0)
  BEGIN
   SET @RE=@RE+SUBSTRING(@FormatNum,LEN(@FormatNum)+1-@LenNum,1)
   IF @LenNum!=1
   BEGIN
    IF @FormatType=101 OR @FormatType=102 OR @FormatType = 103
    BEGIN
     SET @RE=@RE+SUBSTRING(@String2,@LenNum-1,1)
    END
    ELSE IF @FormatType=201 OR @FormatType=202 OR @FormatType = 203
    BEGIN
     SET @RE=@RE+SUBSTRING(@String3,@LenNum-1,1)
    END
   END
   SET @LenNum=@LenNum-1
  END

  --处理整数返回格式
  IF @FormatType=101 OR @FormatType=102 OR @FormatType = 103
  BEGIN
   SELECT @RE = Replace(@RE,N'零千零百零十零亿',N'亿')
   SELECT @RE = Replace(@RE,N'零百零十零亿',N'亿')
   SELECT @RE = Replace(@RE,N'零十零亿',N'亿')
   SELECT @RE = Replace(@RE,N'零亿',N'亿')
   SELECT @RE = Replace(@RE,N'零千零百零十零万',N'万')
   SELECT @RE = Replace(@RE,N'零百零十零万',N'万')
   SELECT @RE = Replace(@RE,N'零十零万',N'万')
   SELECT @RE = Replace(@RE,N'零万',N'万')
   SELECT @RE = Replace(@RE,N'零千零百零十零',N'')
   SELECT @RE = Replace(@RE,N'亿万',N'亿')
   SELECT @RE = Replace(@RE,N'零百零十零',N'')
   SELECT @RE = Replace(@RE,N'零十零',N'')
   SELECT @RE = Replace(@RE,N'零',N'')
   SELECT @RE = Replace(@RE,N'千百',N'千零')
   SELECT @RE = Replace(@RE,N'亿千零十',N'亿零')
   SELECT @RE = Replace(@RE,N'千零十',N'千零')
   SELECT @RE = Replace(@RE,N'万千零',N'万零')
   SELECT @RE = Replace(@RE,N'亿千零',N'亿零')
   SELECT @RE = Replace(@RE,N'亿千',N'亿零')
   SELECT @RE = Replace(@RE,N'万千',N'万零')
   SELECT @RE = Replace(@RE,N'百十',N'百零')
  END
  ELSE IF @FormatType=201 OR @FormatType=202 OR @FormatType = 203
  BEGIN
   SELECT @RE = Replace(@RE,N'零仟零佰零拾零亿',N'亿')
   SELECT @RE = Replace(@RE,N'零佰零拾零亿',N'亿')
   SELECT @RE = Replace(@RE,N'零拾零亿',N'亿')
   SELECT @RE = Replace(@RE,N'零亿',N'亿')
   SELECT @RE = Replace(@RE,N'零仟零佰零拾零万',N'万')
   SELECT @RE = Replace(@RE,N'零佰零拾零万',N'万')
   SELECT @RE = Replace(@RE,N'零拾零万',N'万')
   SELECT @RE = Replace(@RE,N'零万',N'万')
   SELECT @RE = Replace(@RE,N'零仟零佰零拾零',N'')
   SELECT @RE = Replace(@RE,N'亿万',N'亿')
   SELECT @RE = Replace(@RE,N'零佰零拾零',N'')
   SELECT @RE = Replace(@RE,N'零拾零',N'')
   SELECT @RE = Replace(@RE,N'零',N'')
   SELECT @RE = Replace(@RE,N'仟佰',N'仟零')
   SELECT @RE = Replace(@RE,N'亿千零拾',N'亿零')
   SELECT @RE = Replace(@RE,N'仟零拾',N'仟零')
   SELECT @RE = Replace(@RE,N'万仟零',N'万零')
   SELECT @RE = Replace(@RE,N'亿仟零',N'亿零')
   SELECT @RE = Replace(@RE,N'亿仟',N'亿零')
   SELECT @RE = Replace(@RE,N'万仟',N'万零')
   SELECT @RE = Replace(@RE,N'佰拾',N'佰零')
  END

  IF (@FormatType=101 OR @FormatType=103 OR @FormatType=201 OR @FormatType=203) AND LEN(@DecimalPlace)!=0
  BEGIN
   --重置小数位长度
   SELECT @LenNum=LEN(@DecimalPlace)

   IF @FormatType=101 OR @FormatType=201
   BEGIN
    SET @RE=@RE+N'.'+@DecimalPlace
   END
   ELSE IF @FormatType=103 OR @FormatType=203
   BEGIN
    WHILE (@LenNum >=0)
    BEGIN
     SET @RE=@RE+SUBSTRING(@DecimalPlace,LEN(@DecimalPlace)-@LenNum-1,1)
     SET @RE=@RE+SUBSTRING(@String1,LEN(@DecimalPlace)-@LenNum,1)
     SET @LenNum=@LenNum-1
    END
   END
   --处理小数返回格式
   IF @FormatType=101 OR @FormatType=201
   BEGIN
    SELECT @RE = Replace(@RE,N'.零零零零',N'')
   END
   ELSE IF @FormatType=103 OR @FormatType=203
   BEGIN
    SELECT @RE = Replace(@RE,N'零角零分零厘零',N'整')
    SELECT @RE = Replace(@RE,N'零角零分零厘',N'整')
    SELECT @RE = Replace(@RE,N'零角零分',N'零')
    SELECT @RE = Replace(@RE,N'零角',N'零')
    SELECT @RE = Replace(@RE,N'零分零厘',N'')
    SELECT @RE = Replace(@RE,N'零厘',N'')
    SELECT @RE = Replace(@RE,N'零分',N'零')
    SELECT @RE = Replace(@RE,N'元零零',N'零')
   END
  END