Creating a function in SQL Server to convert numbers to words can be quite useful, especially for generating checks, invoices, or reports. Here’s a step-by-step guide and a sample SQL Server function to achieve this. Following the function creation, you’ll find an article that you can use for posting on your website.
SQL Server Function: Convert Numbers to Words
Example read to dollar:
952234.19 is Nine Hundred Fifty Two Thousand Two Hundred Thirty Four Dollars And Nineteen Cents
First, we need to create a SQL Server function that can convert numbers to words. Below is a comprehensive function that handles numbers up to the billions and supports decimals:
Option 1 : Convert Numbers to Words (as Dollar $ and Cents)
ALTER function [dbo].[FN_NumToWords](@Number Numeric(18,2),@CDollar 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 + ' Hundred'
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) + 'Dollars' --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) + 'Dollars'
END
ELSE
BEGIN
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
Select @SNumToWords = @SNumToWords + @Wwords + Space(1) + 'Dollars'
END
END
IF @CDollar = '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) + 'Cents'
END
END
return LTrim(RTrim(@SNumToWords))
ENDTesting:
Select dbo.FN_NumToWords(952234.19,'Y')Result:
Nine Hundred Fifty Two Thousand Two Hundred Thirty Four Dollars And Nineteen CentsOption 2: Sample Convert Numbers to Words (as Cents)
When developing applications such as invoicing systems, generating checks, or financial reports, converting numbers into words can be a crucial feature. This is particularly important for ensuring clarity and avoiding misunderstandings. In this article, we’ll walk you through creating a SQL Server function that converts numbers to words, supporting both whole numbers and decimals.
Why Convert Numbers to Words?
- Check Writing: Ensures that the amount is correctly interpreted.
- Legal Documents: Often require the amount in words to avoid discrepancies.
- Reports and Invoices: Adds professionalism and clarity.
Creating the Function
We’ll start by creating a function named NumberToWords that handles numbers up to billions and includes fractional parts (cents).
Step-by-Step Code
1-Main Function: NumberToWords
CREATE FUNCTION dbo.NumberToWords (@Number DECIMAL(18, 2))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Words NVARCHAR(MAX)
DECLARE @IntegerPart BIGINT
DECLARE @FractionPart INT
-- Splitting the number into integer and fractional parts
SET @IntegerPart = FLOOR(@Number)
SET @FractionPart = ROUND((@Number - @IntegerPart) * 100, 0)
-- Convert integer part to words
SET @Words = dbo.IntegerToWords(@IntegerPart)
-- Handle fractional part if present
IF @FractionPart > 0
BEGIN
SET @Words = @Words + ' and ' + dbo.IntegerToWords(@FractionPart) + ' Cents'
END
RETURN @Words
END
GO
-- Function to convert integer to words
CREATE FUNCTION dbo.IntegerToWords (@Number BIGINT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Words NVARCHAR(MAX)
SET @Words = ''
IF @Number = 0
BEGIN
SET @Words = 'Zero'
END
ELSE
BEGIN
-- Define arrays for words
DECLARE @Ones TABLE (ID INT, Word NVARCHAR(20))
INSERT INTO @Ones VALUES (1, 'One'), (2, 'Two'), (3, 'Three'), (4, 'Four'), (5, 'Five'),
(6, 'Six'), (7, 'Seven'), (8, 'Eight'), (9, 'Nine')
DECLARE @Teens TABLE (ID INT, Word NVARCHAR(20))
INSERT INTO @Teens VALUES (10, 'Ten'), (11, 'Eleven'), (12, 'Twelve'), (13, 'Thirteen'), (14, 'Fourteen'),
(15, 'Fifteen'), (16, 'Sixteen'), (17, 'Seventeen'), (18, 'Eighteen'), (19, 'Nineteen')
DECLARE @Tens TABLE (ID INT, Word NVARCHAR(20))
INSERT INTO @Tens VALUES (2, 'Twenty'), (3, 'Thirty'), (4, 'Forty'), (5, 'Fifty'),
(6, 'Sixty'), (7, 'Seventy'), (8, 'Eighty'), (9, 'Ninety')
DECLARE @Thousands TABLE (ID INT, Word NVARCHAR(20))
INSERT INTO @Thousands VALUES (1, 'Thousand'), (2, 'Million'), (3, 'Billion')
-- Function logic to build words
WHILE @Number > 0
BEGIN
DECLARE @Part INT
DECLARE @WordPart NVARCHAR(100)
-- Billions
IF @Number >= 1000000000
BEGIN
SET @Part = FLOOR(@Number / 1000000000)
SET @Number = @Number % 1000000000
SET @WordPart = dbo.IntegerToWords(@Part) + ' Billion'
END
-- Millions
ELSE IF @Number >= 1000000
BEGIN
SET @Part = FLOOR(@Number / 1000000)
SET @Number = @Number % 1000000
SET @WordPart = dbo.IntegerToWords(@Part) + ' Million'
END
-- Thousands
ELSE IF @Number >= 1000
BEGIN
SET @Part = FLOOR(@Number / 1000)
SET @Number = @Number % 1000
SET @WordPart = dbo.IntegerToWords(@Part) + ' Thousand'
END
-- Hundreds
ELSE IF @Number >= 100
BEGIN
SET @Part = FLOOR(@Number / 100)
SET @Number = @Number % 100
SET @WordPart = dbo.IntegerToWords(@Part) + ' Hundred'
END
-- Tens
ELSE IF @Number >= 20
BEGIN
SET @Part = FLOOR(@Number / 10)
SET @Number = @Number % 10
SELECT @WordPart = Word FROM @Tens WHERE ID = @Part
END
-- Teens
ELSE IF @Number >= 10
BEGIN
SET @Part = @Number
SET @Number = 0
SELECT @WordPart = Word FROM @Teens WHERE ID = @Part
END
-- Ones
ELSE
BEGIN
SET @Part = @Number
SET @Number = 0
SELECT @WordPart = Word FROM @Ones WHERE ID = @Part
END
-- Combine parts
SET @Words = @Words + CASE WHEN LEN(@Words) > 0 THEN ' ' ELSE '' END + @WordPart
END
END
RETURN @Words
END
GO
2-Helper Function: IntegerToWords
CREATE FUNCTION dbo.IntegerToWords (@Number BIGINT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Words NVARCHAR(MAX)
SET @Words = ''
IF @Number = 0
BEGIN
SET @Words = 'Zero'
END
ELSE
BEGIN
-- Define arrays for words
DECLARE @Ones TABLE (ID INT, Word NVARCHAR(20))
INSERT INTO @Ones VALUES (1, 'One'), (2, 'Two'), (3, 'Three'), (4, 'Four'), (5, 'Five'),
(6, 'Six'), (7, 'Seven'), (8, 'Eight'), (9, 'Nine')
DECLARE @Teens TABLE (ID INT, Word NVARCHAR(20))
INSERT INTO @Teens VALUES (10, 'Ten'), (11, 'Eleven'), (12, 'Twelve'), (13, 'Thirteen'), (14, 'Fourteen'),
(15, 'Fifteen'), (16, 'Sixteen'), (17, 'Seventeen'), (18, 'Eighteen'), (19, 'Nineteen')
DECLARE @Tens TABLE (ID INT, Word NVARCHAR(20))
INSERT INTO @Tens VALUES (2, 'Twenty'), (3, 'Thirty'), (4, 'Forty'), (5, 'Fifty'),
(6, 'Sixty'), (7, 'Seventy'), (8, 'Eighty'), (9, 'Ninety')
DECLARE @Thousands TABLE (ID INT, Word NVARCHAR(20))
INSERT INTO @Thousands VALUES (1, 'Thousand'), (2, 'Million'), (3, 'Billion')
-- Function logic to build words
WHILE @Number > 0
BEGIN
DECLARE @Part INT
DECLARE @WordPart NVARCHAR(100)
-- Billions
IF @Number >= 1000000000
BEGIN
SET @Part = FLOOR(@Number / 1000000000)
SET @Number = @Number % 1000000000
SET @WordPart = dbo.IntegerToWords(@Part) + ' Billion'
END
-- Millions
ELSE IF @Number >= 1000000
BEGIN
SET @Part = FLOOR(@Number / 1000000)
SET @Number = @Number % 1000000
SET @WordPart = dbo.IntegerToWords(@Part) + ' Million'
END
-- Thousands
ELSE IF @Number >= 1000
BEGIN
SET @Part = FLOOR(@Number / 1000)
SET @Number = @Number % 1000
SET @WordPart = dbo.IntegerToWords(@Part) + ' Thousand'
END
-- Hundreds
ELSE IF @Number >= 100
BEGIN
SET @Part = FLOOR(@Number / 100)
SET @Number = @Number % 100
SET @WordPart = dbo.IntegerToWords(@Part) + ' Hundred'
END
-- Tens
ELSE IF @Number >= 20
BEGIN
SET @Part = FLOOR(@Number / 10)
SET @Number = @Number % 10
SELECT @WordPart = Word FROM @Tens WHERE ID = @Part
END
-- Teens
ELSE IF @Number >= 10
BEGIN
SET @Part = @Number
SET @Number = 0
SELECT @WordPart = Word FROM @Teens WHERE ID = @Part
END
-- Ones
ELSE
BEGIN
SET @Part = @Number
SET @Number = 0
SELECT @WordPart = Word FROM @Ones WHERE ID = @Part
END
-- Combine parts
SET @Words = @Words + CASE WHEN LEN(@Words) > 0 THEN ' ' ELSE '' END + @WordPart
END
END
RETURN @Words
END
GO
Testing, You can test the function with different numbers to see the output:
SELECT dbo.NumberToWords(123456789.45) AS WordsResult numbers to words
One Hundred Twenty Three Million Four Hundred Fifty Six Thousand Seven Hundred Eighty Nine and Forty Five CentsConclusion
This comprehensive function covers the conversion of numbers to words for both integer and decimal values. By integrating this function into your SQL Server, you can enhance the functionality of your financial applications, ensuring clarity and professionalism in your outputs.
Related Articles
- How to find SQL Server Configuration Manager in Windows 10
- How to generate dates schedule between from start date to end date
- How to split string in SQL Server
- Insert Random Number With String Into A Table In SQL Server (Loop)
- How To Find Table Name Or Column Name By Data Or Value In Table | SQL Server
- How to Shrink the Database Or Files In SQL Server
