Hàm chuyển số thành chữ trong tiếng việt dùng MS SQL

Mục đích

Dùng để in ấn các hóa đơn từ một số ra chữ.

-- Đọc nhóm 3 chữ số --

-- by code4viet --

create  function [dbo].[fc_NumberToWordsGroup]

(

@Level int, 

@Number int,

@MaxLevel int

)

returns nvarchar(256)

AS

begin

declare @LevelTable table

(

EnumValue int,

EnumName nvarchar(256)

)

Insert into @LevelTable select 1,N''

Insert into @LevelTable select 2,N'nghìn'

Insert into @LevelTable select 3,N'triệu'

Insert into @LevelTable select 4,N'tỷ'

declare @NumberTable table

(

EnumValue int,

EnumName nvarchar(256)

)

Insert into @NumberTable select 0,N'không'

Insert into @NumberTable select 1,N'một'

Insert into @NumberTable select 2,N'hai'

Insert into @NumberTable select 3,N'ba'

Insert into @NumberTable select 4,N'bốn'

Insert into @NumberTable select 5,N'năm'

Insert into @NumberTable select 6,N'sáu'

Insert into @NumberTable select 7,N'bảy'

Insert into @NumberTable select 8,N'tám'

Insert into @NumberTable select 9,N'chín'

declare @NumberTable2 table

(

EnumValue int,

EnumName nvarchar(256)

)

Insert into @NumberTable2 select 0,N''

Insert into @NumberTable2 select 1,N'mốt'

Insert into @NumberTable2 select 2,N'hai'

Insert into @NumberTable2 select 3,N'ba'

Insert into @NumberTable2 select 4,N'bốn'

Insert into @NumberTable2 select 5,N'lăm'

Insert into @NumberTable2 select 6,N'sáu'

Insert into @NumberTable2 select 7,N'bảy'

Insert into @NumberTable2 select 8,N'tám'

Insert into @NumberTable2 select 9,N'chín'

Insert into @NumberTable2 select 10,N'mười'

Insert into @NumberTable2 select 20,N'hai mươi'

Insert into @NumberTable2 select 30,N'ba mươi'

Insert into @NumberTable2 select 40,N'bốn mươi'

Insert into @NumberTable2 select 50,N'năm mươi'

Insert into @NumberTable2 select 60,N'sáu mươi'

Insert into @NumberTable2 select 70,N'bảy mươi'

Insert into @NumberTable2 select 80,N'tám mươi'

Insert into @NumberTable2 select 90,N'chín mươi'

Declare @NumberToWordsGroup nvarchar(256)

Set @NumberToWordsGroup=''

Declare @LenNumber int

Set @LenNumber = len(@Number)

if @LenNumber=1

Select @NumberToWordsGroup = [EnumName] From @NumberTable Where EnumValue = @Number

if @Level>0 And @MaxLevel<>@Level

Select @NumberToWordsGroup = N'không trăm lẻ ' + @NumberToWordsGroup

if @LenNumber=2

Begin

Select @NumberToWordsGroup = [EnumName] From @NumberTable2 Where EnumValue = @Number - @Number%10

if (@Number%10>0)

Select @NumberToWordsGroup = @NumberToWordsGroup + ' ' + [EnumName] From @NumberTable2 Where EnumValue = @Number%10

if @Number = 11

Select @NumberToWordsGroup = N'mười một'

if @Level>0 And @MaxLevel<>@Level

Select @NumberToWordsGroup = N'không trăm '+ @NumberToWordsGroup

End

if @LenNumber=3

Begin

Select @NumberToWordsGroup = [EnumName] + N' trăm' From @NumberTable Where EnumValue = (@Number - @Number%100)/100

-- Nếu hàng đơn vị <10 thì phải đọc là "lẻ" --

if ((@Number%100)<10)

if (@Number%100)=0

Select @NumberToWordsGroup = @NumberToWordsGroup

else

Begin

Select @NumberToWordsGroup = @NumberToWordsGroup + N' lẻ ' + [EnumName] From @NumberTable Where EnumValue = (@Number%100)%10

End

else

begin

Select @NumberToWordsGroup = @NumberToWordsGroup + ' ' + [EnumName] From @NumberTable2 Where EnumValue = @Number%100 - (@Number%100)%10

if (@Number%100) = 11

Select @NumberToWordsGroup = @NumberToWordsGroup + N' một'

else

Select @NumberToWordsGroup = @NumberToWordsGroup + ' ' + [EnumName] From @NumberTable2 Where EnumValue = (@Number%100)%10

end

end

Select @NumberToWordsGroup = @NumberToWordsGroup + ' ' + [EnumName] From @LevelTable Where EnumValue = @Level

Return rtrim(ltrim(@NumberToWordsGroup))

End

GO

-- Đọc số ra chữ --

-- by code4viet

create   function [dbo].[fc_NumberToWords](@Number Decimal(28,6))

returns nvarchar(256)

AS

begin

-- -- Test--

-- Declare @Number Decimal(28,6)

-- Set @Number = -1000000090.00000

-- --End test --

Declare @NumberToWords nvarchar(256)

Declare @DecimalNumberToWords nvarchar(256)

Declare @DecimalNumber Decimal(28,6)

Declare @DigitNumber nvarchar(256)

Declare @ThreeNumber bigint

Set @NumberToWords = ''

Set @DecimalNumberToWords = ''

if (@Number<0)

begin

set @NumberToWords='âm'

set @Number = -@Number

end

-- Cắt phần thập phân --

Set @DigitNumber = floor(@Number)

Set @DecimalNumber = @Number - @DigitNumber

----------------------------

-- Tính số nhóm --

if (len(@DigitNumber)%3=1)

Set @DigitNumber = '00'+ @DigitNumber

if (len(@DigitNumber)%3=2)

Set @DigitNumber = '0'+ @DigitNumber

Declare @LevelNumber int

Declare @MaxLevel int

Set @LevelNumber =  (len(@DigitNumber)/3) 

Set @MaxLevel = @LevelNumber

-- Xử lý theo từng nhóm 3 số --

if @MaxLevel>1

while (@LevelNumber>0)

begin

Set @ThreeNumber = cast(substring(@DigitNumber,1,3) as bigint)

if (@ThreeNumber<>0 )

Select @NumberToWords = @NumberToWords + ' ' + dbo.fc_NumberToWordsGroup(@LevelNumber, @ThreeNumber,@MaxLevel)

-- select @LevelNumber,@ThreeNumber, dbo.fc_NumberToWordsGroup(@LevelNumber, @ThreeNumber,@MaxLevel)

Set @DigitNumber = substring(@DigitNumber,4,@LevelNumber*3)

Set @LevelNumber=@LevelNumber-1

end

else

Select @NumberToWords = @NumberToWords + ' ' + dbo.fc_NumberToWordsGroup(0, @DigitNumber,0)

-- Kết thúc -- Xử lý theo từng nhóm 3 số --

-- Xử lý phần số thập phân --

if (@DecimalNumber>0)

Begin

Declare @StrDecimalNumber nvarchar(256)

Set @DecimalNumberToWords=N'phẩy'

Set @StrDecimalNumber = cast(@DecimalNumber as nvarchar(256))

Set @StrDecimalNumber = substring(@StrDecimalNumber,3,len(@StrDecimalNumber))

While(@StrDecimalNumber>0)

Begin

Select @DecimalNumberToWords = @DecimalNumberToWords + ' ' + dbo.fc_NumberToWordsGroup(0, substring(@StrDecimalNumber,1,1),0)

Set @StrDecimalNumber = Substring(@StrDecimalNumber,2,len(@StrDecimalNumber))

End

Select @NumberToWords = @NumberToWords + ' ' + @DecimalNumberToWords

End

-- Kết thúc -- Xử lý phần số thập phân --

Select @NumberToWords = rtrim(ltrim(@NumberToWords))

-- Select @NumberToWords + @DecimalNumberToWords,@DecimalNumber,@Number

return Upper(substring(@NumberToWords,1,1)) + substring(@NumberToWords,2,len(@NumberToWords))

end

GO

-- test --

select [dbo].[fc_NumberToWords](100000)