SQL scalar value function: Lowercase amount to uppercase

Our daily development Business system, as a statistical report, especially financial statements, showing the amount of Chinese is often encountered.

There are a number of ways to convert case, and the following is a solution to this problem from a database function.

Effect

Call: SELECT dbo. [FN_CONVERTRMB] (192.4)

The specific functions are as follows:

-- =============================================
--Invoke: SELECT dbo. [FN_CONVERTRMB] (192.4)
--Create date:2015-01-06
--Description: Return Chinese amount
-- =============================================
CREATE FUNCTION [dbo]. [FN_CONVERTRMB] (@num NUMERIC (14, 2))
RETURNS VARCHAR (100)
As
BEGIN
DECLARE @n_data VARCHAR (20),
@c_data VARCHAR (100),
@n_str VARCHAR (10),
@i INT
SET @n_data =right (SPACE) +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 @[email protected]_data+substring (' 0 a three-Woolu qi Ba Jiu ', CAST (@n_str as INT) +1, 1)
IF not ((@n_str = ' 0 ')
and (@i<>4)
and (@i<>8)
and (@i<>12))
SET @[email protected]_data+substring (' Qian Bai hundred million thousand hundred thousand Gather round corner ', @i, 1)
IF SUBSTRING (@c_data, LEN (@c_data)-1, 2) = ' billions '
SET @c_data =substring (@c_data, 1, LEN (@c_data)-1)
END
SET @[email protected]+1
END
IF @num <0
SET @c_data = ' (negative number) ' [Email protected]_data
IF @num =0
SET @c_data = ' 0 Circle '
IF @n_str = ' 0 '
SET @[email protected]_data+ ' whole '
RETURN (@c_data)
END

If friends think that may be used later, welcome reprint, of course, can also recommend, thank you.

PS: This blog post is written using Windows Live Writer 2012, and the format effect may not be good.

SQL scalar value function: Lowercase amount to uppercase