如何在 Sql Server 中实现货币格式的相关推荐

如何在 Sql Server 中实现货币格式的相关推荐

Create FUNCTION fn_ChnMoney(@decNum decimal(18,2))
RETURNS varchar(200)
AS
BEGIN

DECLARE @chvNum varchar(200)
DECLARE @chvMoney varchar(200)
DECLARE @chvTemp varchar(200)
DECLARE @intIntLen int
DECLARE @intI int
DECLARE @chvTempI varchar(200)
DECLARE @chvReturn varchar(200)
DECLARE @sFsFlag int

SET @sFsFlag = 0

IF @decNum=0
SET @chvReturn = '零'
Else
BEGIN

IF @decNum<0
BEGIN
SET @sFsFlag = 1
SET @decNum = ABS(@decNum)
END

SET @chvTemp=convert(varchar(200),Round(@decNum*100,0))
IF charindex('.',@chvTemp,1)>0
SET @chvNum=left(@chvTemp,charindex('.',@chvTemp,1)-1)
ELSE
SET @chvNum=@chvTemp

SET @intIntLen=len(@chvNum)

SET @chvMoney=''
Set @chvReturn = ''
SET @intI=1

WHILE @intI <= @intIntLen
BEGIN
SET @chvTempI = substring(@chvNum,@intIntLen-@intI+1,1)
SET @chvMoney = substring('零壹贰叁肆伍陆柒捌玖',convert(int,@chvTempI)+1,1) + substring('分角元拾佰仟万拾佰仟亿拾佰仟兆拾佰仟京拾佰仟',(@intI-1)+1,1)
SET @intI = @intI + 1

SET @chvReturn = @chvMoney + @chvReturn
END
END

SET @chvReturn=Replace(@chvReturn,'零仟','零')
SET @chvReturn=Replace(@chvReturn,'零佰','零')
SET @chvReturn=Replace(@chvReturn,'零拾','零')

while charindex('零零',@chvReturn,1)>0
SET @chvReturn=Replace(@chvReturn,'零零','零')

SET @chvReturn=Replace(@chvReturn,'零京','京')
SET @chvReturn=Replace(@chvReturn,'零兆','兆')
SET @chvReturn=Replace(@chvReturn,'零亿','亿')
SET @chvReturn=Replace(@chvReturn,'零万','万')

SET @chvReturn=Replace(@chvReturn,'京兆','京')
SET @chvReturn=Replace(@chvReturn,'京亿','京')
SET @chvReturn=Replace(@chvReturn,'京万','京')
SET @chvReturn=Replace(@chvReturn,'兆亿','兆')
SET @chvReturn=Replace(@chvReturn,'兆万','兆')
SET @chvReturn=Replace(@chvReturn,'亿万','亿')

SET @chvReturn=Replace(@chvReturn,'零元','元')
SET @chvReturn=Replace(@chvReturn,'零角零分','整')
SET @chvReturn=Replace(@chvReturn,'零角','零')
SET @chvReturn=Replace(@chvReturn,'零分','整')
SET @chvReturn=LTRIm(RTRIm(@chvReturn))

IF @sFsFlag = 1
SET @chvReturn = '负' + Ltrim(@chvReturn)

RETURN @chvReturn

END