SQL Server function converts renminbi numbers to uppercase

SQL Server function converts renminbi numbers to uppercase

CREATE FUNCTION [dbo]. [F_num_chn] (@num numeric (14,5))

RETURNS varchar with encryption

As

BEGIN

DECLARE @n_data VARCHAR (VARCHAR), @c_data (a), @n_str VARCHAR (a), @i int

SET @n_data =right (+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) = ') or

((@n_str = ' 0 ') and ((@i=4) or (@i=8) or (@i=12) or (@i=14)))

SET @c_data = @c_data +substring (' 0 Woolu ba Nine ', CAST (@n_str as int) +1,1)

IF not ((@n_str = ' 0 ') and (@i<>4) and (@i<>8) and (@i<>12))

SET @c_data = @c_data +substring (' Thousand Hundred million thousand thousand hundred to collect the rounded corner cent ', @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 @i=@i+1

End

IF @num <0

SET @c_data = ' (negative number) ' + @c_data

IF @num =0

SET @c_data = ' 0 Circle '

IF @n_str = ' 0 '

SET @c_data = @c_data + ' whole '

Return (@c_data)

End

SQL Server function converts renminbi numbers to uppercase

Idle is also idle, welcome everyone to mention valuable advice

--Arabic numerals to Chinese capital numbers

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= ' One '

If @n=2

Set @s= ' II '

If @n=3

Set @s= ' ' Three '

If @n=4

Set @s= ' ' Restaurant '

If @n=5

Set @s= ' ' Wu '

If @n=6

Set @s= ' Land '

If @n=7

Set @s= ' Qi '

If @n=8

Set @s= ' BA '

If @n=9

Set @s= ' ' Nine '

Return @s

End

--can support to the billion

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

--up to 9 digits

If @k=9

Set @r=@r+

Dbo. NTOCC (CONVERT (int,substring (REVERSE (@i), @k,1))

--@i

+ ' billion '

If @k=8

Set @r=@r+dbo. NTOCC (CONVERT (int,substring (REVERSE (@i), @k,1)) + ' thousand '

If @k=7

Set @r=@r+dbo. NTOCC (CONVERT (int,substring (REVERSE (@i), @k,1)) + ' Bai '

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)) + ' million '

If @k=4

Set @r=@r+dbo. NTOCC (CONVERT (int,substring (REVERSE (@i), @k,1)) + ' thousand '

If @k=3

Set @r=@r+dbo. NTOCC (CONVERT (int,substring (REVERSE (@i), @k,1)) + ' Bai '

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)) + ' Yuan '

Set @k=@k-1

End

If convert (float,@f) =0

Set @r=@r+ ' whole '

Else

Begin

If substring (@s,1,1) > ""

Set @r=@r+dbo. NTOCC (CONVERT (int,substring (@f,1,1))) + ' Corner '

If substring (@s,2,1) > ""

Set @r=@r+dbo. NTOCC (CONVERT (int,substring (@f,2,1))) + ' min '

End