Solution

gaoqingfeng

Thank youkxiaperfor providing

But the same problem still exists
select dbo.ChangeBigSmall(101000.00)
--result
One thousand ten thousand yuan

kxiaper

Thank youkxiaperfor providing

But the same problem still exists
select dbo.ChangeBigSmall(101000.00)
--result
One thousand ten thousand yuan
-------------------------------------------------- ------
what is the problem?
Is it like this? Ten thousand yuan

gaoqingfeng

select dbo.ChangeBigSmall(101000.00)
--result
One thousand ten thousand yuan


The desired result should be: one thousand ten thousand yuan (the above result is zero)

waxiaoning

alter function to_up(@num numeric(14,2))
returns varchar(100)
as
begin
declare @ndata varchar(20), @cdata varchar(100)
declare @nstr varchar(10),@zflag bit,@t varchar(10),@i int
set @ndata=RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100) AS bigint) AS varchar(20)), 14)
set @cdata=''
set @zflag=0; set @i=1

while @i<=14
  begin
   set @nstr=substring(@ndata,@i,1)
   if @nstr<>' '
   begin
    --digital
    set @nstr=SUBSTRING('Zero One Two Three Lu Wu Qiu Jiu Jiu', CAST(@nstr AS int)+1,1)
    set @t=SUBSTRING('Thousand and ten million ten thousand and ten thousand and ten thousand round corner points', @i,1)
    if @nstr='zero'
      begin
       if @t in ('100 million', '10 thousand','round')
set @cdata=@cdata+@t
       if @zflag=0
          set @zflag=1
      end
    else
      begin
        if @zflag=1
begin
           if @t='round'
            set @cdata=@cdata+@t
           else
            set @cdata=@cdata+'zero'+@nstr+@t

           set @zflag=0
end
        else
         set @cdata=@cdata+@nstr+@t
      end
   end
   set @i=@i+1
  end
IF @num<0
    SET @cdata='(negative number)'+@cdata
  IF @num=0
    SET @cdata='zero circle'
  IF @nstr='zero'
    SET @cdata=@cdata+'integer'
  RETURN(@cdata)
end

gaoqingfeng

There is a problem with single digits upstairs:

select dbo.to_up(101.00)

--result
-------------
One hundred round

(The number of rows affected is 1 row)

waxiaoning

if @t='round'
            set @cdata=@cdata+@t
           else
            set @cdata=@cdata+'zero'+@nstr+@t
-->
set @cdata=@cdata+'zero'+@nstr+@t

kxiaper

In fact, "ten thousand" and "thousand" are next to each other, there is no need to add "zero" in the middle
I do not know the intention of the landlord to do so

Just like "1100" does not need to be displayed as "One Thousand and One Hundred"