SQL - How to convert a Number amount to Words Amount in Transact-SQL?


(Kunni Can) #1

How can i convert a numbered amount into Words amount in Transact-SQL,

Something similar to: “12” ==> “Twelve”.

Thanks.


(Yay) #2

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