SQL server function converts the number of RMB into a large write format
Create function [dbo]. [f_num_chn] (@ num numeric (14,5 ))
RETURNS varchar (100) WITH ENCRYPTION
AS
BEGIN
DECLARE @ n_data VARCHAR (20), @ c_data VARCHAR (100), @ n_str VARCHAR (10), @ I int
SET @ n_data = RIGHT (SPACE (14) + 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 @ c_data = @ c_data + SUBSTRING)
IF not (@ n_str = '0') and (@ I <> 4) and (@ I <> 8) and (@ I <> 12 ))
SET @ c_data = @ c_data + SUBSTRING)
If substring (@ c_data, LEN (@ c_data)-1, 2) = 'billionaire'
SET @ c_data = SUBSTRING (@ c_data, 1, LEN (@ c_data)-1)
END
SET @ I = @ I + 1
END
IF @ num <0
SET @ c_data = '(negative number)' + @ c_data
IF @ num = 0
SET @ c_data = 'circle 0'
IF @ n_str = '0'
SET @ c_data = @ c_data + 'Integral'
RETURN (@ c_data)
END
SQL server function converts the number of RMB into a large write format
Idle and idle. You are welcome to give more valuable comments.
-- Convert Arabic numerals to uppercase Chinese numerals
Create function NToCC (@ n int)
Returns varchar (2)
As
Begin
Declare @ s varchar (2)
If @ n = 0
Set @ s = ''0''
If @ n = 1
Set @ s = ''''
If @ n = 2
Set @ s = ''er''
If @ n = 3
Set @ s = ''3''
If @ n = 4
Set @ s = ''ta''
If @ n = 5
Set @ s = ''wo''
If @ n = 6
Set @ s = ''loan''
If @ n = 7
Set @ s = ''then''
If @ n = 8
Set @ s = ''then''
If @ n = 9
Set @ s = ''then''
Return @ s
End
-- Supports hundreds of millions
Create function numbertoChineseBigString (@ m money)
Returns varchar (255)
Begin
Declare @ I varchar (255)
Declare @ f varchar (255)
Declare @ r varchar (255)
Declare @ s varchar (255)
Set @ s = convert (varchar (255), @ m)
If charindex (''.'', @ s)> 0
Begin
Set @ I = substring (@ s, 1, charindex ('.', @ s)-1)
Set @ f = substring (@ s, charindex ('.', @ s) + 1, len (@ s ))
End
Else
Set @ I = @ s
Set @ r = ''''
Declare @ k int
Set @ k = len (@ I)
While @ k> = 1
Begin
-- A maximum of 9 digits
If @ k = 9
Set @ r = @ r +
Dbo. NToCC (convert (int, substring (REVERSE (@ I), @ k, 1 )))
-- @ I
+ ''''
If @ k = 8
Set @ r = @ r + dbo. NToCC (convert (int, substring (REVERSE (@ I), @ k, 1) + ''kil''
If @ k = 7
Set @ r = @ r + dbo. NToCC (convert (int, substring (REVERSE (@ I), @ k, 1) + ''failed''
If @ k = 6
Set @ r = @ r + dbo. NToCC (convert (int, substring (REVERSE (@ I), @ k, 1) + ''pick''
If @ k = 5
Set @ r = @ r + dbo. NToCC (convert (int, substring (REVERSE (@ I), @ k, 1) + ''10''
If @ k = 4
Set @ r = @ r + dbo. NToCC (convert (int, substring (REVERSE (@ I), @ k, 1) + ''kil''
If @ k = 3
Set @ r = @ r + dbo. NToCC (convert (int, substring (REVERSE (@ I), @ k, 1) + ''failed''
If @ k = 2
Set @ r = @ r + dbo. NToCC (convert (int, substring (REVERSE (@ I), @ k, 1) + ''pick''
If @ k = 1
Set @ r = @ r + dbo. NToCC (convert (int, substring (REVERSE (@ I), @ k, 1) + ''meta''
Set @ k = @ K-1
End
If convert (float, @ f) = 0
Set @ r = @ r + ''integral''
Else
Begin
If substring (@ s, 1, 1)> ''''
Set @ r = @ r + dbo. NToCC (convert (int, substring (@ f, 1, 1) + ''''
If substring (@ s, 2, 1)> ''''
Set @ r = @ r + dbo. NToCC (convert (int, substring (@ f, 2, 1) + ''points''
End