SQLSERVER function converts the number of RMB into a large write format

The SQL Server function converts the number of RMB into a large write format: CREATEFUNCTION [dbo]. [f_num_chn] (@ numnumeric (100) RETURNSvarchar (100) returns @ n_dataVARCHAR (20), @ c_dataVARCHAR (), @ n_strVARCHAR (10), @ iintSET @ n_dataRIGHT (S)

The SQL SERVER FUNCTION converts a Renminbi number into a large write form. CREATE FUNCTION [dbo]. [f_num_chn] (@ num numeric (100) RETURNS varchar (100) with encryption as begin declare @ n_data VARCHAR (20), @ c_data VARCHAR ), @ n_str VARCHAR (10), @ I int SET @ n_data = RIGHT (S

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