Solution

gaoqingfeng

How to convert dec (19,2) type value to uppercase RMB value
Such as
0.05 ------------ Wufen
0.00 --------- Zero circle
10.00 ---------- Round
1000000000 ----------- Ten million
101.05 ------------- One hundred and one rounds and five corners

The value is converted to regular capitalization!
Thank you

gaoqingfeng

It's been a day since I wrote it, but I didn't find a good solution when dealing with values ​​with 0 interruptions!

gaoqingfeng

gahade(沙果) () Reputation: 100 Blog 2017-01-05 18:26:22 Score: 0


   http://www.qiuhao.com/dispbbs.asp?BoardID=15&ID=3833
  
Executed it according to what you gave
declare @ret varchar(200)

exec L2U 1001.00,1,@ret output

select @ret

--result
One thousand and one hundred and one hundred and zero

Actually what I want is: one thousand and one yuan

gaoqingfeng

Thank you gahade (沙果) ()
If you only want to achieve this result: one hundred and one hundred and one hundred and one yuan and zero and zero points, it is really a lot of trouble

恶魔右指

I even bookmarked one:
CREATE FUNCTION [dbo].[f_num_chn] (@num numeric(14,2))
RETURNS varchar(100) WITH ENCRYPTION
AS
BEGIN
--Copyright: pbsql
  DECLARE @n_data VARCHAR(20),@c_data VARCHAR(100),@n_str VARCHAR(10),@i int

  SET @n_data=RIGHT(SPACE(14)+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 @c_data=@c_data+SUBSTRING('Zero One Two Three Lu Wuqiu Jiu Jiu', 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('Thousands of millions of thousands of corners of rounded corners', @i,1)
      IF SUBSTRING(@c_data,LEN(@c_data)-1,2)='Billion'
        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='Zero Circle'
  IF @n_str='0'
    SET @c_data=@c_data+'integer'
  RETURN(@c_data)
END

gaoqingfeng

I found that my thinking is very similar to his, for example, using the'Qianbaishiyiqianbaiqianbaiqianbaiqian round corner points' string to judge ((@n_str='0') and (@i<>4) and ( @i<>8) and (@i<>12)), but I judge 1,5,9,13.
But when I deal with multiple consecutive zeros, the processing is incorrect, and pbsql writes much simpler than me! It is worth collecting and learning...

There is also a bug in the method I wrote in pbsql, that is, when @n_str=0,4,8,12, at this time the value of the value at this position will be a problem

If execute select dbo.f_num_chn(101000.00)
--result
One thousand ten thousand round

In fact, I think the result should be: one thousand ten thousand and one thousand round

gaoqingfeng

The master to fix the small bug of the above function
He wrote it too concisely.
I am not capable enough to change

lixidams

This is indeed a rather troublesome question!
I haven’t solved it a while ago, ashamed~~~

wgfxman

Ha ha. . ~
Make a small suggestion:
The last'integer' should be only if there are no decimals or 0 after the decimal point

kxiaper

The landlord then refers to this:
--Create function
Create Function ChangeBigSmall(@ChangeMoney money)
Returns VarChar(100) AS

Begin
Declare @String1 char(20)
    Declare @String2 char(30)
    Declare @String4 Varchar(100)
    Declare @String3 Varchar(100) - the value taken from the original A value
    Declare @i int - loop variable
    Declare @J Int - The value of A multiplied by the string length of 100
    Declare @Ch1 Varchar(100) - Chinese reading of numbers
    Declare @Ch2 Varchar(100) - Chinese character reading of digits
    Declare @Zero Int - used to calculate how many consecutive zeros
    Declare @ReturnValue VarChar(100)

    Select @ReturnValue =''
    Select @String1 ='Zero One Two Three Lu Wu Qiu Jiu Jiu'
    Select @ String2 = 'Wan Qian Bai picked up 1,000 billion Bai Bai Qian Wan pick up ten-dollar arc minutes'

    Select @String4 = Cast(@ChangeMoney*100 as int)
    Select @J=len(cast((@ChangeMoney*100) as int))
    Select @String2=Right(@String2,@J)
    Select @i = 1

    While @i<= @j
Begin
Select @String3 = Substring(@String4,@i,1)

        If @String3<>'0'
Begin
Select @Ch1 = Substring(@String1, Cast(@String3 as Int) + 1, 1)
Select @Ch2 = Substring(@String2, @i, 1)
Select @Zero = 0-indicates that the standard is not zero
End
        Else
Begin
If (@Zero = 0) Or (@i = @J-9) Or (@i = @J-5) Or (@i = @J-1)
Select @Ch1 ='zero'
Else
Select @Ch1 =''
Select @Zero = @Zero + 1 - indicates that the standard is 0
The
--If the converted value needs to be expanded, then the value of the following expression I needs to be changed.
Select @Ch2 =''
The
                If @i = @J-10
Begin
                    Select @Ch2 ='Billion'
                        Select @Zero = 0
        End
                    
If @i = @J-6
Begin
Select @Ch2 ='Wan'
Select @Zero = 0
End
The
If @i = @J-2
Begin
Select @Ch2 ='Yuan'
Select @Zero = 0
End
The
If @i = @J
Select @Ch2 ='Full'
        End

        Select @ReturnValue = @ReturnValue + @Ch1 + @Ch2
        Select @i = @i+1
    End

    - Finally remove the extra zeros
    If CharIndex('仟仟',@ReturnValue) <> 0
            Select @ReturnValue = Replace(@ReturnValue,'仟仟','仟')
    If CharIndex('百百',@ReturnValue) <> 0
            Select @ReturnValue = Replace(@ReturnValue,'Baibai','Bai')
    If CharIndex('Zero Yuan', @ReturnValue) <> 0
            Select @ReturnValue = Replace(@ReturnValue,'zero yuan','yuan')
    If CharIndex('Zero Thousand',@ReturnValue) <> 0
            Select @ReturnValue = Replace(@ReturnValue,'zero ten thousand','ten thousand')
    If CharIndex('Zero Billion', @ReturnValue) <> 0
            Select @ReturnValue = Replace(@ReturnValue,'Zero Billion','Billion')
    If CharIndex('Zero integer',@ReturnValue) <> 0
            Select @ReturnValue = Replace(@ReturnValue,'zero integer','integer')
    If CharIndex('Zero Bai',@ReturnValue) <> 0
            Select @ReturnValue = Replace(@ReturnValue,'Zero Bai','Zero')
    If CharIndex('零仟',@ReturnValue) <> 0
            Select @ReturnValue = Replace(@ReturnValue,'零仟','zero')
    If CharIndex('元元',@ReturnValue) <> 0
            Select @ReturnValue = Replace(@ReturnValue,'元元','元')
    Return @ReturnValue
End
go

--Call functions
select dbo.ChangeBigSmall(1000101.567)
go

--Delete function
drop function ChangeBigSmall

--View Results
/*
One hundred and one hundred and one hundred and one yuan
*/