Number expressed as words
-
We need to express an integer (eg 1034) as words ("one thousand and thirty four").
Does anyone here know of anything that'll do this conversion for us, or are we going to have to figure it out for ourselves?
Thanks!
Thomas Rushton
blog: https://thelonedba.wordpress.com -
This was removed by the editor as SPAM
-
This is how I do it with Money values to word. WIth a few changes you should have exactly what you need.
-----------------Code-------------------
--You will need this table.--
CREATE TABLE NumNameTbl (
[val] [int] NOT NULL,
[numname] [varchar](20) NOT NULL,
CONSTRAINT [PK_NumNameTbl_val] PRIMARY KEY CLUSTERED
(
[val]
) WITH FILLFACTOR = 100
)
GO
/* Insert the value records, yes this is a heap table */
INSERT INTO NumNameTbl (val,numname) VALUES (1,'One')
INSERT INTO NumNameTbl (val,numname) VALUES (2,'Two')
INSERT INTO NumNameTbl (val,numname) VALUES (3,'Three')
INSERT INTO NumNameTbl (val,numname) VALUES (4,'Four')
INSERT INTO NumNameTbl (val,numname) VALUES (5,'Five')
INSERT INTO NumNameTbl (val,numname) VALUES (6,'Six')
INSERT INTO NumNameTbl (val,numname) VALUES (7,'Seven')
INSERT INTO NumNameTbl (val,numname) VALUES (8,'Eight')
INSERT INTO NumNameTbl (val,numname) VALUES (9,'Nine')
INSERT INTO NumNameTbl (val,numname) VALUES (10,'Ten')
INSERT INTO NumNameTbl (val,numname) VALUES (11,'Eleven')
INSERT INTO NumNameTbl (val,numname) VALUES (12,'Twelve')
INSERT INTO NumNameTbl (val,numname) VALUES (13,'Thirteen')
INSERT INTO NumNameTbl (val,numname) VALUES (14,'Fourteen')
INSERT INTO NumNameTbl (val,numname) VALUES (15,'Fifteen')
INSERT INTO NumNameTbl (val,numname) VALUES (16,'Sixteen')
INSERT INTO NumNameTbl (val,numname) VALUES (17,'Seventeen')
INSERT INTO NumNameTbl (val,numname) VALUES (18,'Eightteen')
INSERT INTO NumNameTbl (val,numname) VALUES (19,'Nineteen')
INSERT INTO NumNameTbl (val,numname) VALUES (20,'Twenty')
INSERT INTO NumNameTbl (val,numname) VALUES (30,'Tirty')
INSERT INTO NumNameTbl (val,numname) VALUES (40,'Fourty')
INSERT INTO NumNameTbl (val,numname) VALUES (50,'Fifty')
INSERT INTO NumNameTbl (val,numname) VALUES (60,'Sixty')
INSERT INTO NumNameTbl (val,numname) VALUES (70,'Seventy')
INSERT INTO NumNameTbl (val,numname) VALUES (80,'Eighty')
INSERT INTO NumNameTbl (val,numname) VALUES (90,'Ninty')
GO
/* The procedure. */
CREATE PROCEDURE ip_ReturnMoneyName
@Money Money
AS
SET NOCOUNT ON
DECLARE @MonName VARCHAR(8000)
DECLARE @MonStr VARCHAR(100)
DECLARE @TempMon VARCHAR(3)
SET @MonStr = CAST(@Money AS VARCHAR(100))
SET @MonName = ''
IF CAST(RIGHT(@MonStr,2) AS INT) > 0
BEGIN
SET @TempMon = RIGHT(@MonStr,2)
SET @MonName = 'Cents'
SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(@TempMon,1) WHEN 1 THEN @TempMon ELSE RIGHT(@TempMon,1) END)
IF LEFT(@TempMon,1) NOT IN (0,1)
SELECT @MonName = numname + (CASE LTRIM(@MonName) WHEN 'CENTS' THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(@TempMon,1) + '0') AS int)
IF LEN(@MonStr) = 4 AND LEFT(@MonStr,1) = 0
BEGIN
SET @MonName = @MonName
END
ELSE
SET @MonName = 'And ' + @MonName
END
SET @MonStr = LEFT(@MonStr,LEN(@MonStr) - 3)
/* Ones, Tens Hundreds */
IF LEN(@MonStr) > 0
BEGIN
SET @TempMon = RIGHT(@MonStr,3)
SET @MonStr = LEFT(@MonStr, LEN(@MonStr) - LEN(@TempMon))
If CAST(@TempMon AS INT) != 0
BEGIN
SET @MonName = 'Dollars ' + @MonName
SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(RIGHT(@TempMon,2),1) WHEN 1 THEN RIGHT(@TempMon,2) ELSE RIGHT(@TempMon,1) END)
IF LEN(@TempMon) > 1
BEGIN
IF LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)
SELECT @MonName = numname + (CASE RIGHT(@TempMon,1) WHEN 0 THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(RIGHT(@TempMon,2),1) + '0') AS int)
END
IF CHARINDEX('Dollars',@MonName) > 1 AND (LEN(@TempMon) = 3 OR LEN(@MonStr) > 0)
IF CHARINDEX(' And ',@MonName) = 0
SET @MonName = 'And ' + @MonName
IF LEN(@TempMon) = 3 AND CAST(LEFT(@TempMon,1) AS INT) != 0
SELECT @MonName = numname + ' Hundred ' + @MonName FROM NumNameTbl WHERE val = CAST(LEFT(@TempMon,1) AS INT)
END
END
/* Thousands */
IF LEN(@MonStr) > 0
BEGIN
SET @TempMon = RIGHT(@MonStr,3)
SET @MonStr = LEFT(@MonStr, LEN(@MonStr) - LEN(@TempMon))
If CAST(@TempMon AS INT) != 0
BEGIN
IF CHARINDEX('Dollars',@MonName) = 0
SET @MonName = 'Dollars ' + @MonName
SET @MonName = 'Thousand ' + @MonName
SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(RIGHT(@TempMon,2),1) WHEN 1 THEN RIGHT(@TempMon,2) ELSE RIGHT(@TempMon,1) END)
IF LEN(@TempMon) > 1
BEGIN
IF LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)
SELECT @MonName = numname + (CASE RIGHT(@TempMon,1) WHEN 0 THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(RIGHT(@TempMon,2),1) + '0') AS int)
END
IF LEN(@TempMon) = 3 AND CAST(LEFT(@TempMon,1) AS INT) != 0
SELECT @MonName = numname + ' Hundred ' + @MonName FROM NumNameTbl WHERE val = CAST(LEFT(@TempMon,1) AS INT)
END
END
/* Million */
IF LEN(@MonStr) > 0
BEGIN
SET @TempMon = RIGHT(@MonStr,3)
SET @MonStr = LEFT(@MonStr, LEN(@MonStr) - LEN(@TempMon))
If CAST(@TempMon AS INT) != 0
BEGIN
IF CHARINDEX('Dollars',@MonName) = 0
SET @MonName = 'Dollars ' + @MonName
SET @MonName = 'Million ' + @MonName
SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(RIGHT(@TempMon,2),1) WHEN 1 THEN RIGHT(@TempMon,2) ELSE RIGHT(@TempMon,1) END)
IF LEN(@TempMon) > 1
BEGIN
IF LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)
SELECT @MonName = numname + (CASE RIGHT(@TempMon,1) WHEN 0 THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(RIGHT(@TempMon,2),1) + '0') AS int)
END
IF LEN(@TempMon) = 3 AND CAST(LEFT(@TempMon,1) AS INT) != 0
SELECT @MonName = numname + ' Hundred ' + @MonName FROM NumNameTbl WHERE val = CAST(LEFT(@TempMon,1) AS INT)
END
END
/* Billion */
IF LEN(@MonStr) > 0
BEGIN
SET @TempMon = RIGHT(@MonStr,3)
SET @MonStr = LEFT(@MonStr, LEN(@MonStr) - LEN(@TempMon))
If CAST(@TempMon AS INT) != 0
BEGIN
IF CHARINDEX('Dollars',@MonName) = 0
SET @MonName = 'Dollars ' + @MonName
SET @MonName = 'Billion ' + @MonName
SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(RIGHT(@TempMon,2),1) WHEN 1 THEN RIGHT(@TempMon,2) ELSE RIGHT(@TempMon,1) END)
IF LEN(@TempMon) > 1
BEGIN
IF LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)
SELECT @MonName = numname + (CASE RIGHT(@TempMon,1) WHEN 0 THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(RIGHT(@TempMon,2),1) + '0') AS int)
END
IF LEN(@TempMon) = 3 AND CAST(LEFT(@TempMon,1) AS INT) != 0
SELECT @MonName = numname + ' Hundred ' + @MonName FROM NumNameTbl WHERE val = CAST(LEFT(@TempMon,1) AS INT)
END
END
IF CHARINDEX('One',@MonName) = 1 AND CHARINDEX('Dollars',@MonName) = 5
SET @MonName = REPLACE(@MonName,'Dollars','Dollar')
SELECT @MonName AS MoneyName
GO
-
Antares,
Thanks for that. Sorry I'm a bit late, but it's been a bad week...
Thomas Rushton
blog: https://thelonedba.wordpress.com -
Hope it gives you what you need to get your results and heres to a better week from now on.
-
quote:
We need to express an integer (eg 1034) as words ("one thousand and thirty four").Does anyone here know of anything that'll do this conversion for us, or are we going to have to figure it out for ourselves?
trot on over to the script library or just click the "home" link here. i contributed a user-defined function for that a couple of days ago.
-
quote:
Hope it gives you what you need to get your results and heres to a better week from now on.I don't know about a better week...
I had made a start on this problem just after I posted the request here. However, I had to leave it in a hurry and go on to other things (including hospitals...)
Anyway, herewith my solution (which doesn't use a table, but does use two functions to do the job):
-----code-----
USE css
GO
CREATE FUNCTION dbo.fn_inttowords_subfunction (@val decimal(3,0))
RETURNS varchar(255) AS
BEGIN
declare @Ret varchar(255)
declare @Hundreds varchar(50)
declare @SubHundreds varchar(50)
declare @Units varchar(50)
declare @Dig1 int
declare @Dig2 int
declare @Dig3 int
declare @Digits varchar(3)
declare @LowTwos int
SET @Dig1 = 0
SET @Dig2 = 0
SET @Dig3 = 0
SET @Digits = convert(varchar(3), @val)
if (datalength(@Digits) = 1)
BEGIN
SET @Dig3 = convert(int, @Digits)
END
IF (datalength(@Digits) = 2)
BEGIN
SET @Dig3 = convert(int, SubString(@Digits,2,1))
SET @Dig2 = convert(int, SubString(@Digits, 1, 1))
END
IF (DataLength(@Digits) = 3)
BEGIN
SET @Dig3 = convert(int, SubString(@Digits, 3, 1))
SET @Dig2 = convert(int, Substring(@Digits, 2, 1))
SET @Dig1 = convert(int, Substring(@Digits, 1, 1))
END
SET @LowTwos = convert(int, @Dig2 + @Dig3)
SET @Hundreds =
CASE @Dig1
WHEN 1 THEN 'One Hundred'
WHEN 2 THEN 'Two Hundred'
WHEN 3 THEN 'Three Hundred'
WHEN 4 THEN 'Four Hundred'
WHEN 5 THEN 'Five Hundred'
WHEN 6 THEN 'Six Hundred'
WHEN 7 THEN 'Seven Hundred'
WHEN 8 THEN 'Eight Hundred'
WHEN 9 THEN 'Nine Hundred'
END
IF (@Dig2 > 1)
BEGIN
SET @SubHundreds =
CASE @Dig2
WHEN 2 THEN 'Twenty'
WHEN 3 THEN 'Thirty'
WHEN 4 THEN 'Forty'
WHEN 5 THEN 'Fifty'
WHEN 6 THEN 'Sixty'
WHEN 7 THEN 'Seventy'
WHEN 8 THEN 'Eighty'
WHEN 9 THEN 'Ninety'
END
END
IF (@Dig2 = 1)
BEGIN
SET @SubHundreds =
CASE @Dig2 * 10 + @Dig3
WHEN 19 THEN 'Nineteen'
WHEN 18 THEN 'Eighteen'
WHEN 17 THEN 'Seventeen'
WHEN 16 THEN 'Sixteen'
WHEN 15 THEN 'Fifteen'
WHEN 14 THEN 'Fourteen'
WHEN 13 THEN 'Thirteen'
WHEN 12 THEN 'Twelve'
WHEN 11 THEN 'Eleven'
WHEN 10 THEN 'Ten'
END
END
IF (@Dig2 <> 1)
BEGIN
SET @Units =
CASE @Dig3
WHEN 9 THEN 'Nine'
WHEN 8 THEN 'Eight'
WHEN 7 THEN 'Seven'
WHEN 6 THEN 'Six'
WHEN 5 THEN 'Five'
WHEN 4 THEN 'Four'
WHEN 3 THEN 'Three'
WHEN 2 THEN 'Two'
WHEN 1 THEN 'One'
WHEN 0 THEN ''
END
IF ( (@SubHundreds <> '') AND NOT (@SubHundreds IS NULL) )
BEGIN
SET @SubHundreds = @SubHundreds + ' ' + @Units
END
ELSE
BEGIN
SET @SubHundreds = @Units
END
END
IF ( (@Hundreds = '') OR (@Hundreds IS NULL) )
BEGIN
SET @Ret = @SubHundreds
END
ELSE
BEGIN
SET @Ret = @Hundreds
IF ( (@SubHundreds <> '') AND NOT (@SubHundreds IS NULL) )
BEGIN
SET @Ret = @Ret + ' and ' + @SubHundreds
END
END
return @Ret
END
GO
CREATE FUNCTION dbo.fn_inttowords(@val int)
RETURNS VARCHAR(255) AS
BEGIN
declare @Ret varchar(255)
declare @Ones int
declare @Thousands int
declare @Millions int
declare @Billions int
declare @OnesString varchar(255)
declare @ThousandsString varchar(255)
declare @MillionsString varchar(255)
declare @BillionsString varchar(255)
set @Ones = @val % 1000
set @Thousands = (@val/1000) % 1000
set @Millions = (@val/1000000) % 1000
set @Billions = (@val/1000000000) % 1000
IF (@Billions > 0)
BEGIN
SET @BillionsString = dbo.fn_inttowords_subfunction(@Billions)
END
IF (@Millions > 0)
BEGIN
SET @MillionsString = dbo.fn_inttowords_subfunction(@Millions)
END
IF (@Thousands > 0)
BEGIN
SET @ThousandsString = dbo.fn_inttowords_subfunction(@Thousands)
END
IF (@Ones > 0)
BEGIN
SET @OnesString = dbo.fn_inttowords_subfunction(@Ones)
END
IF NOT (@BillionsString IS NULL)
BEGIN
SET @Ret = @BillionsString + ' Billion'
END
IF NOT (@MillionsString IS NULL)
BEGIN
IF NOT (@Ret IS NULL)
BEGIN
SET @Ret = @Ret + ' ' + @MillionsString + ' Million'
END
ELSE
BEGIN
SET @Ret = @MillionsString + ' Million'
END
END
IF NOT (@ThousandsString IS NULL)
BEGIN
IF NOT (@Ret IS NULL)
BEGIN
SET @Ret = @Ret + ' ' + @ThousandsString + ' Thousand'
END
ELSE
BEGIN
SET @Ret = @ThousandsString + ' Thousand'
END
END
IF NOT (@OnesString IS NULL)
BEGIN
IF NOT (@Ret IS NULL)
BEGIN
SET @Ret = @Ret + ' ' + @OnesString
END
ELSE
BEGIN
SET @Ret = @OnesString
END
END
RETURN IsNull(@Ret, 'zero')
END
Thomas Rushton
blog: https://thelonedba.wordpress.com -
Interesting approach without a table. If you test I suggest posting as an alternative to the scripts section.
-
I have submitted it to the scripts section already.
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply