How can i convert a numbered amount into Words amount in Transact-SQL,
Something similar to: “12” ==> “Twelve”.
Thanks.
2 Likes
You can follow the these steps:
1. Create A Data Table (Temporary one, or within a transaction it’s up to you):
CREATE TABLE Words (
[Code] INT NULL,
[Number] INT NULL,
[Words] VARCHAR(50) NULL
)
2. Insert These Values to the Above Table:
INSERT INTO [Words]
([Code],[Number],[Words])
VALUES
(1,0,'Zero'),
(2,1,'One'),
(3,2,'Two'),
(4,3,'Three'),
(5,4,'Four'),
(6,5,'Five'),
(7,6,'Six'),
(8,7,'Seven'),
(9,8,'Eight'),
(10,9,'Nine'),
(11,10,'Ten'),
(12,11,'Eleven'),
(13,12,'Twelve'),
(14,13,'Thirteen'),
(15,14,'Fourteen'),
(16,15,'Fifteen'),
(17,16,'Sixteen'),
(18,17,'Seventeen'),
(19,18,'Eighteen'),
(20,19,'Nineteen'),
(21,20,'Twenty'),
(22,30,'Thirty'),
(23,40,'Forty'),
(24,50,'Fifty'),
(25,60,'Sixty'),
(26,70,'Seventy'),
(27,80,'Eighty'),
(28,90,'Ninety')
3. And now the important part, the function that does the calculation itself:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [usp_Number2Words](@Number INT, @CPaise CHAR(1))
RETURNS VARCHAR(5000)
BEGIN
DECLARE @StrNumber VARCHAR(10),
@SLacs CHAR(2),
@SThou CHAR(2),
@SHun CHAR(2)
DECLARE @STenUnt CHAR(2),
@STen CHAR(2),
@SUnt CHAR(2),
@SDecimal CHAR(2)
DECLARE @ILacs INT,
@IThou INT,
@IHun INT,
@ITenUnt INT,
@ITen INT,
@IUnt INT,
@IDecimal INT
DECLARE @SNumToWords VARCHAR(100),
@Wwords VARCHAR(10)
Select @StrNumber = Replicate('0',10-Len(LTrim(RTrim(convert(VARCHAR,@Number))))) + LTrim(RTrim(Convert(VARCHAR,@Number)))
Select @SNumToWords = ''
IF Len(LTrim(RTrim(convert(VARCHAR,@Number)))) > 4
BEGIN
Select @SLacs = Substring(@StrNumber,1,2)
Select @ILacs = Convert(int,@SLacs)
IF @ILacs > 0
BEGIN
Select @STen = Substring(@StrNumber,1,1)
Select @SUnt = Substring(@StrNumber,2,1)
IF Convert(int,@STen) = 1
BEGIN
Select @ITen = Convert(int,Substring(@StrNumber,1,2))
Select @IUnt = 0
END
ELSE
BEGIN
Select @ITen = Convert(int,@STen)*10
Select @IUnt = Convert(int,@SUnt)
END
IF @ITen > 0
BEGIN
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ITen
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
END
IF @IUnt > 0
BEGIN
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
END
Select @SNumToWords = @SNumToWords + ' Lacs'
END
Select @SThou = Substring(@StrNumber,3,2)
Select @IThou = Convert(int,@SThou)
IF @IThou > 0
BEGIN
Select @STen = Substring(@StrNumber,3,1)
Select @SUnt = Substring(@StrNumber,4,1)
IF Convert(int,@STen) = 1
BEGIN
Select @ITen = Convert(int,Substring(@StrNumber,3,2))
Select @IUnt = 0
END
ELSE
BEGIN
Select @ITen = Convert(int,@STen)*10
Select @IUnt = Convert(int,@SUnt)
END
IF @ITen > 0
BEGIN
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ITen
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
END
IF @IUnt > 0
BEGIN
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
END
Select @SNumToWords = @SNumToWords + ' Thousand '
END
Select @SHun = Substring(@StrNumber,5,1)
Select @IHun = Convert(int,@SHun)
IF @IHun > 0
BEGIN
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IHun
Select @SNumToWords = @SNumToWords + @Wwords + ' Hundred'
END
Select @STenUnt = Substring(@StrNumber,6,2)
Select @ITenUnt = Convert(int,@STenUnt)
IF @ITenUnt > 0
BEGIN
Select @STen = Substring(@StrNumber,6,1)
Select @SUnt = Substring(@StrNumber,7,1)
IF Convert(int,@STen) = 1
BEGIN
Select @ITen = Convert(int,Substring(@StrNumber,6,2))
Select @IUnt = 0
END
ELSE
BEGIN
Select @ITen = Convert(int,@STen)*10
Select @IUnt = Convert(int,@SUnt)
END
IF @ITen > 0
BEGIN
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ITen
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
END
IF @IUnt > 0
BEGIN
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
END
END
Select @SNumToWords = @SNumToWords + Space(1) + 'Rupees' --Only/-
END
ELSE
BEGIN
Select @SLacs = Substring(LTrim(RTrim(convert(varchar,@Number))),1,1)
Select @ILacs = Convert(int,@SLacs)
IF @ILacs > 0 and @ILacs <> 1
BEGIN
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords + Space(1) + 'Rupees'
END
ELSE
BEGIN
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
Select @SNumToWords = @SNumToWords + @Wwords + Space(1) + 'Rupee'
END
END
IF @CPaise = 'Y'
BEGIN
Select @SDecimal = Substring(@StrNumber,9,2)
Select @IDecimal = Convert(int,@SDecimal)
IF @IDecimal > 0
BEGIN
Select @SNumToWords = @SNumToWords + ' and'
Select @STen = Substring(@SDecimal,1,1)
Select @SUnt = Substring(@SDecimal,2,1)
IF Convert(int,@STen) = 1
BEGIN
Select @ITen = Convert(int,Substring(@StrNumber,9,2))
Select @IUnt = 0
END
ELSE
BEGIN
Select @ITen = Convert(int,@STen)*10
Select @IUnt = Convert(int,@SUnt)
END
IF @ITen > 0
BEGIN
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ITen
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
END
IF @IUnt > 0
BEGIN
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
END
Select @SNumToWords = @SNumToWords + Space(1) + 'Paise'
END
END
return LTrim(RTrim(@SNumToWords))
END
4. That’s it, you only Call that function with the Appropriate Numeric values
1 Like