sql 金额数字转换大写

你可以使用以下SQL函数将金额数字转换为大写：

CREATE FUNCTION dbo.ConvertToWords (@NumValue DECIMAL(19, 2))
RETURNS NVARCHAR(4000)
AS BEGIN
    DECLARE @WholePart BIGINT
    DECLARE @FractionalPart INT
    DECLARE @Words NVARCHAR(4000)

    SELECT @WholePart = CONVERT(BIGINT, FLOOR(@NumValue))
    SELECT @FractionalPart = CONVERT(INT, (@NumValue - @WholePart) * 100)

    -- Whole part conversion
    SELECT @Words = dbo.ConvertNumber(@WholePart) + ' dollars '

    -- Fractional part conversion
    IF @FractionalPart > 0 BEGIN
        SELECT @Words = @Words + 'and ' + dbo.ConvertNumber(@FractionalPart) + ' cents'
    END

    RETURN @Words
END
GO

CREATE FUNCTION dbo.ConvertNumber (@Number BIGINT)
RETURNS NVARCHAR(4000)
AS BEGIN
    DECLARE @Units TABLE (UnitName NVARCHAR(10))
    DECLARE @Tens TABLE (TenName NVARCHAR(10))
    DECLARE @Teens TABLE (TeenName NVARCHAR(10))

    INSERT INTO @Units VALUES ('Zero'), ('One'), ('Two'), ('Three'), ('Four'), 
        ('Five'), ('Six'), ('Seven'), ('Eight'), ('Nine')

    INSERT INTO @Tens VALUES ('Ten'), ('Twenty'), ('Thirty'), ('Forty'), 
        ('Fifty'), ('Sixty'), ('Seventy'), ('Eighty'), ('Ninety')

    INSERT INTO @Teens VALUES ('Eleven'), ('Twelve'), ('Thirteen'), 
        ('Fourteen'), ('Fifteen'), ('Sixteen'), ('Seventeen'), 
        ('Eighteen'), ('Nineteen')

    DECLARE @Words NVARCHAR(4000)

    IF @Number >= 1000000000 BEGIN
        SELECT @Words = dbo.ConvertNumber(@Number / 1000000000) + ' billion '
        SELECT @Number = @Number % 1000000000
    END

    IF @Number >= 1000000 BEGIN
        SELECT @Words = @Words + dbo.ConvertNumber(@Number / 1000000) + ' million '
        SELECT @Number = @Number % 1000000
    END

    IF @Number >= 1000 BEGIN
        SELECT @Words = @Words + dbo.ConvertNumber(@Number / 1000) + ' thousand '
        SELECT @Number = @Number % 1000
    END

    IF @Number >= 100 BEGIN
        SELECT @Words = @Words + dbo.ConvertNumber(@Number / 100) + ' hundred '
        SELECT @Number = @Number % 100
    END

    IF @Number >= 20 BEGIN
        SELECT @Words = @Words + (SELECT TenName FROM @Tens WHERE (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) = (@Number / 10)) + ' '
        SELECT @Number = @Number % 10
    END

    IF @Number > 10 AND @Number < 20 BEGIN
        SELECT @Words = @Words + (SELECT TeenName FROM @Teens WHERE (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) = (@Number - 10)) + ' '
        SELECT @Number = 0
    END

    IF @Number > 0 BEGIN
        SELECT @Words = @Words + (SELECT UnitName FROM @Units WHERE (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) = @Number) + ' '
    END

    RETURN RTRIM(@Words)
END
GO

要使用这个函数，你可以像这样调用它：

SELECT dbo.ConvertToWords(12345.67) AS AmountInWords

该函数将返回 "Twelve thousand three hundred forty-five dollars and sixty-seven cents"。

请注意，此示例仅处理最多十亿美元的金额。如果需要处理更大的金额，请根据需要进行修改。