Location>code7788 >text

SQLSEVER realize currency figures to Chinese characters

Popularity：277 ℃/2024-10-18 14:51:17

SQLSEVER to convert numbers to Chinese (currency)

-- =============================================
-- Author: LearnerPing
-- Create date: 2024/10/18
-- Description: Change Number to Chinese
-- =============================================
Create FUNCTION GetNumberToChi
(
@p_amt NUMERIC(19,6)
)
RETURNS NVARCHAR(200) -- Returns the data type of the return value
AS
BEGIN
    DECLARE @p_amt_str NUMERIC(19,2)
    DECLARE @returnStr NVARCHAR(200).

    -- return '##########' if the amount is greater than or equal to 100 million
    IF (@p_amt >= 1000000000)
SET @returnStr = N'##########';
ELSE
        -- otherwise, return '00000000000'
        SET @returnStr = N'00000000000';

    SET @p_amt_str = ROUND(@p_amt,2)
    -- Convert the amount to a 12-bit string, right-justified
    SET @returnStr = RIGHT(@returnStr + CAST(ROUND(@p_amt_str, 11, 2) AS NVARCHAR(12)), 12);

    -- Replace numbers with Chinese numerals
    SET @returnStr = REPLACE(@returnStr, N'0', N'zero'); -- Replace number with Chinese digit.
    SET @returnStr = REPLACE(@returnStr, N'1', N'One'); SET @returnStr = REPLACE(@returnStr, N'1', N'One')
    SET @returnStr = REPLACE(@returnStr, N'2', N'Two');
    
    
    SET @returnStr = REPLACE(@returnStr, N'5', N'5');
    
    SET @returnStr = REPLACE(@returnStr, N'7', N'7');
    SET @returnStr = REPLACE(@returnStr, N'8', N'eight');
    SET @returnStr = REPLACE(@returnStr, N'9', N'nine'); SET @returnStr = REPLACE(@returnStr, N'9', N'nine');

    -- Add units
    SET @returnStr = @returnStr + N'cents';
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 1, 0, N'angle'); -- Add units.
    SET @returnStr = REPLACE(@returnStr, N'.' , N'yuan'); SET @returnStr = REPLACE(@returnStr, N'.'.

    -- Add the unit of the amount
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 5, 0, N'pickup'); -- add unit of amount
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 7, 0, N'Hundred');
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 9, 0, N'Thousand');
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 11, 0, N'Thousand'); SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 11, 0, N'Thousand'); SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 11, 0, N'Thousand')
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 13, 0, N'pickup'); SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 13, 0, N'pickup');
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 15, 0, N'hundred'); SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 15, 0, N'hundred')
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 17, 0, N'Thousand'); SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 17, 0, N'Thousand');
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 19, 0, N'billion'); SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 19, 0, N'billion');

    -- Replace zeros and units
    SET @returnStr = REPLACE(@returnStr, N'zero billion', N'zero');; -- replace zero and units
    SET @returnStr = REPLACE(@returnStr, N'zero-thousand', N'zero'); SET @returnStr = REPLACE(@returnStr, N'zero-thousand', N'zero')
    SET @returnStr = REPLACE(@returnStr, N'zero hundred', N'zero'); SET @returnStr = REPLACE(@returnStr, N'zero hundred', N'zero')
    SET @returnStr = REPLACE(@returnStr, N'zero-pick', N'zero'); SET @returnStr = REPLACE(@returnStr, N'zero-pick', N'zero');
    SET @returnStr = REPLACE(@returnStr, N'zero-zero', N'zero'); SET @returnStr = REPLACE(@returnStr, N'zero-zero', N'zero');
    SET @returnStr = REPLACE(@returnStr, N'zero-zero', N'zero'); SET @returnStr = REPLACE(@returnStr, N'zero-zero', N'zero');
    SET @returnStr = REPLACE(@returnStr, N'zero million', N'ten thousand'); SET @returnStr = REPLACE(@returnStr, N'zero million', N'ten thousand');
    SET @returnStr = REPLACE(@returnStr, N'zero dollars', N'yuan'); SET @returnStr = REPLACE(@returnStr, N'zero dollars', N'yuan');
    SET @returnStr = REPLACE(@returnStr, N'zero dimes', N'zeros'); SET @returnStr = REPLACE(@returnStr, N'zero dimes', N'zeros');
    SET @returnStr = REPLACE(@returnStr, N'zero cents', N'');

    -- Replace ten cents and hundred cents
    SET @returnStr = REPLACE(@returnStr, N'tenths', N'tenths');; -- replace tenths and hundredths
    SET @returnStr = REPLACE(@returnStr, N'hundredths', N'hundredths');;

    -- Remove extra zeros
    WHILE (LEFT(@returnStr, 1) = N'zero')
BEGIN
        SET @returnStr = RIGHT(@returnStr, LEN(@returnStr) - 1); -- remove extra zeros WHILE (LEFT(@returnStr, 1) = N'zero') BEGIN
    END

    IF (LEFT(@returnStr, 1) = N'million')
        SET @returnStr = RIGHT(@returnStr, LEN(@returnStr) - 1);


    IF (LEFT(@returnStr, 1) = N'$')
        SET @returnStr = RIGHT(@returnStr, LEN(@returnStr) - 1);


    WHILE (LEFT(@returnStr, 1) = N'zero')
BEGIN
        SET @returnStr = RIGHT(@returnStr, LEN(@returnStr) - 1);
    END

    WHILE (RIGHT(@returnStr, 1) = N'zero')
BEGIN
        SET @returnStr = LEFT(@returnStr, LEN(@returnStr) - 1);
    END

    -- Replace billions and zeros
    SET @returnStr = REPLACE(@returnStr, N'billions', N'billions and zeroes');
    SET @returnStr = REPLACE(@returnStr, N'zero-dollar', N'dollar');

    -- If the return string is empty, return 'zero dollars'
    IF (@returnStr = N'')
        SET @returnStr = N'zero dollars';


    -- add 'positive' if it ends in a dollar
    IF (RIGHT(@returnStr, 1) = N'dollar')
        SET @returnStr = @returnStr + N'positive';

    RETURN @returnStr;
END;

Can be converted while retaining two decimal places

<Previous Next>