--------- SQL server function converts the number of RMB into a large write form -- r

Create function [dbo]. [f_num_chn] (@ num numeric (14,5 ))
RETURNS varchar (100) WITH ENCRYPTION
AS
BEGIN
-- Copyright: weihuachao@sina.com
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

Set @ r = Replace (@ r, ''zero, zero '')
Set @ r = Replace (@ r, ''zero refresh 0'', ''zero '')
Set @ r = Replace (@ r, '' '', ''0 '')
Set @ r = Replace (@ r, ''zero pick'', ''zero '')
Set @ r = replace (@ r, ''0 k'', ''0 '')
Set @ r = replace (@ r, '' '', ''0 '')
Set @ r = replace (@ r, 'Pick up 100', ''pick up 100 '')
Return @ r

End

-- Select dbo. numbertoChineseBigString (100102)

Article Source: http://www.diybl.com/course/7_databases/ SQL /sqlServer/2008224/101116.html

 

Create function dbo. L2U (@ n_LowerMoney numeric (15,2), @ v_TransType int)

Returns varchar (200)

BEGIN

Declare @ v_LowerStr VARCHAR (200) -- lower case amount

Declare @ v_UpperPart VARCHAR (200)

Declare @ v_UpperStr VARCHAR (200) -- Capital Amount

Declare @ I _ I int

Set @ v_LowerStr = LTRIM (RTRIM (ROUND (@ n_LowerMoney, 2) -- returns the specified precision and deletes the Left and Right spaces of the data.

Set @ I _ I = 1

Set @ v_UpperStr =''

While (@ I _ I <= len (@ v_LowerStr ))

Begin

Select @ v_UpperPart = case substring (@ v_LowerStr, len (@ v_LowerStr)-@ I _ I + 1,1)

WHEN '. 'then' yuan'

WHEN '0' THEN '0'

WHEN '1' THEN 'yi'

WHEN '2' THEN 'er'

WHEN '3' THEN 'san'

WHEN '4' then'

WHEN '5' then' Wu'

WHEN '6' THEN 'l'

WHEN '7' then' THEN'

WHEN '8' then' THEN'

WHEN '9' then' THEN'

END

+

Case @ I _ I

WHEN 1 THEN 'Min'

WHEN 2 THEN 'angular'

WHEN 3 then''

WHEN 4 then''

WHEN 5 THEN 'start'

WHEN 6 THEN 'hangzhou'

WHEN 7 THEN 'hangzhou'

WHEN 8 THEN 'wane'

WHEN 9 THEN 'start'

WHEN 10 THEN 'hangzhou'

WHEN 11 THEN 'hangzhou'

WHEN 12 THEN '100'

When 13 then 'start'

When 14 then 'hangzhou'

When 15 then 'hangzhou'

When 16 then 'wane'

Else''

End

Set @ v_upperstr = @ v_upperpart + @ v_upperstr

Set @ I _ I = @ I _ I + 1

End

If (0 = @ v_transtype)

Begin

Set @ v_upperstr = Replace (@ v_upperstr, 'zero', '0 ')

Set @ v_upperstr = Replace (@ v_upperstr, 'zero failed', '0 ')

Set @ v_upperstr = Replace (@ v_upperstr, 'zero failed', '0 ')

Set @ v_upperstr = Replace (@ v_upperstr, 'zero zero 0', 'zero ')

Set @ v_upperstr = Replace (@ v_upperstr, '0', '0 ')

Set @ v_upperstr = Replace (@ v_upperstr, 'zero angle and zero point', 'Integral ')

Set @ v_upperstr = Replace (@ v_upperstr, 'zero point', 'Integral ')

Set @ v_upperstr = Replace (@ v_upperstr, '0', '0 ')

Set @ v_upperstr = Replace (@ v_upperstr, '100, 0 billion, 0 thousand, RMB ')

Set @ v_UpperStr = REPLACE (@ v_UpperStr, '100 million RMB 0 thousand ', '100 billion RMB ')

Set @ v_UpperStr = REPLACE (@ v_UpperStr, '2017 11111100', '100% ')

Set @ v_UpperStr = REPLACE (@ v_UpperStr, '000000', '000000 ')

Set @ v_UpperStr = REPLACE (@ v_UpperStr, '10 thousand yuan ', '10 thousand yuan ')

Set @ v_UpperStr = REPLACE (@ v_UpperStr, '000000', '100 ')

Set @ v_UpperStr = REPLACE (@ v_UpperStr, '2013', 'wan ')

Set @ v_UpperStr = REPLACE (@ v_UpperStr, '0 meta', 'meta ')

Set @ v_UpperStr = REPLACE (@ v_UpperStr, '0', '0 ')

End

-- Processing of less than 1 RMB

If ('meta' = substring (@ v_UpperStr, 1, 1 ))

Begin

Set @ v_UpperStr = substring (@ v_UpperStr, 2, (len (@ v_UpperStr)-1 ))

End

If ('0' = substring (@ v_UpperStr, 1, 1 ))

Begin

Set @ v_UpperStr = substring (@ v_UpperStr, 2, (len (@ v_UpperStr)-1 ))

End

If ('angular '= substring (@ v_UpperStr, 1, 1 ))

Begin

Set @ v_UpperStr = substring (@ v_UpperStr, 2, (len (@ v_UpperStr)-1 ))

End

If ('sub' = substring (@ v_UpperStr ))

Begin

Set @ v_UpperStr = substring (@ v_UpperStr, 2, (len (@ v_UpperStr)-1 ))

End

If ('Integral '= substring (@ v_UpperStr ))

Begin

Set @ v_UpperStr = 'zero yuan integral'

End

Return @ v_UpperStr

END