SET STATISTICS TIME ON
SELECT N, N * N AS N_SQUARED, dbo.fnSpellInteger(N * N) AS WORDS
FROM dbo.Tally
SET STATISTICS TIME OFF


(11000 row(s) affected)

SQL Server Execution Times:
   CPU time = 13681 ms,  elapsed time = 14052 ms.

CREATE FUNCTION dbo.fnIntegerInWords ( @number int )
RETURNS varchar(100)
AS
BEGIN
/* -- PUT slash asterisk at the beginning of this line, but for testing remove it
DECLARE @number int
SET @number = 123456789
*/ -- PUT asterisk slash at the beginning of this line, but for testing remove it
IF @number < 0 RETURN 'ERROR, NEGATIVE NUMBER USED'
IF @number = 0 RETURN 'Zero'

DECLARE @result varchar(100), @cn varchar(12)
DECLARE @NUMWORDS TABLE (
	Num varchar(2) NOT NULL PRIMARY KEY CLUSTERED,
	Word varchar(9)
)
INSERT INTO @NUMWORDS (Num, Word)
SELECT '0', '' UNION ALL
SELECT '1', 'One' UNION ALL
SELECT '2', 'Two' UNION ALL
SELECT '3', 'Three' UNION ALL
SELECT '4', 'Four' UNION ALL
SELECT '5', 'Five' UNION ALL
SELECT '6', 'Six' UNION ALL
SELECT '7', 'Seven' UNION ALL
SELECT '8', 'Eight' UNION ALL
SELECT '9', 'Nine' UNION ALL
SELECT '10', 'Ten' UNION ALL
SELECT '11', 'Eleven' UNION ALL
SELECT '12', 'Twelve' UNION ALL
SELECT '13', 'Thirteen' UNION ALL
SELECT '14', 'Fourteen' UNION ALL
SELECT '15', 'Fifteen' UNION ALL
SELECT '16', 'Sixteen' UNION ALL
SELECT '17', 'Seventeen' UNION ALL
SELECT '18', 'Eighteen' UNION ALL
SELECT '19', 'Nineteen' UNION ALL
SELECT '2_', 'Twenty' UNION ALL
SELECT '3_', 'Thirty' UNION ALL
SELECT '4_', 'Forty' UNION ALL
SELECT '5_', 'Fifty' UNION ALL
SELECT '6_', 'Sixty' UNION ALL
SELECT '7_', 'Seventy' UNION ALL
SELECT '8_', 'Eighty' UNION ALL
SELECT '9_', 'Ninety'

SET @cn = RIGHT('00000000000' + CAST(@number AS VARCHAR(10)),12) -- Pad the left with zeros to make the length divisible by 3.

DECLARE @STR TABLE (
	RN int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
	STRING char(3) NOT NULL,
	WORD AS CAST(CASE RN WHEN 1 THEN ' Billion' WHEN 2 THEN ' Million' WHEN 3 THEN ' Thousand' WHEN 4 THEN '' END AS varchar(9))
)

INSERT INTO @STR (STRING)
SELECT SUBSTRING(@cn,T.N,3) AS STRING
FROM dbo.Tally AS T
WHERE T.N < 11 AND (T.N % 3) = 1

/* -- Remove for testing
SELECT *
FROM @STR
*/ -- Remove for testing

SET @result = ''
SELECT @result = @result +
	-- FIRST DIGIT OF THREE
	CASE LEFT(S.STRING,1)
		WHEN '0' THEN ''
		ELSE (SELECT Word FROM @NUMWORDS WHERE Num = LEFT(S.STRING,1)) + ' Hundred '
	END +
	-- DIGITS TWO AND THREE
	CASE
		WHEN RIGHT(S.STRING,2) = '00' THEN ''
		WHEN RIGHT(S.STRING,2) LIKE '0_' THEN (SELECT Word FROM @NUMWORDS WHERE Num = RIGHT(S.STRING,1)) 
		ELSE (SELECT Word FROM @NUMWORDS WHERE LEN(Num) = 2 AND RIGHT(S.STRING,2) LIKE Num) +
			CASE
				WHEN SUBSTRING(S.STRING,2,1) <> '1' AND RIGHT(S.STRING,1) <> '0' THEN '-' + (SELECT Word FROM @NUMWORDS WHERE Num = RIGHT(S.STRING,1))
				ELSE ''
			END
	END +
	-- DIGIT GROUP VALUE
	CASE
		WHEN S.STRING = '000' THEN ''
		ELSE S.WORD
	END + ' '
FROM @STR AS S
RETURN LTRIM(RTRIM(@result))

END
GO

SET STATISTICS TIME ON
SELECT N, N * N AS N_SQUARED, dbo.fnIntegerInWords(N * N) AS WORDS
FROM dbo.Tally
SET STATISTICS TIME OFF

(11000 row(s) affected)

SQL Server Execution Times:
   CPU time = 4992 ms,  elapsed time = 5213 ms.

SELECT dbo.fnSpellInteger ( 1999999999 )

SELECT LEN(dbo.fnSpellInteger ( 1777777000 ) + ' ' + dbo.fnSpellInteger ( 777 ))

declare @numwords table(number int, ones varchar(15),  tens varchar(15), hundreds varchar(15), thousands varchar(15))
insert into @numwords
select 0,NULL,NULL,NULL,NULL union all
 select 1,'One','Ten','One Hundred','One Thousand' union all
 select 2,'Two','Twenty','Two Hundred','Two Thousand' union all
 select 3,'Three','Thirty','Three Hundred','Three Thousand' union all
 select 4,'Four','Forty','Four Hundred','Four Thousand'  union all
 select 5,'Five','Fifty','Five Hundred','Five Thousand'  union all
 select 6,'Six','Sixty','Six Hundred','Six Thousand'  union all
 select 7,'Seven','Seventy','Seven Hundred','Seven Thousand'  union all
 select 8,'Eight','Eighty','Eight Hundred','Eight Thousand' union all
 select 9,'Nine','Ninety','Nine Hundred','NineThousand' union all
 select 10,'Nineteen',NULL,NULL,NULL union all
 select 11,'Eleven',NULL,NULL,NULL union all
 select 12,'Twelve',NULL,NULL,NULL union all
 select 13,'Thirteen',NULL,NULL,NULL union all
 select 14,'Fourteen',NULL,NULL,NULL union all
 select 15,'Fifteen',NULL,NULL,NULL union all
 select 16,'Sixteen',NULL,NULL,NULL union all
 select 17,'Seventeen',NULL,NULL,NULL union all
 select 18,'Eighteen',NULL,NULL,NULL union all
 select 19,'Nineteen',NULL,NULL,NULL 

set statistics time on;
	with expanded as 
	(select N
			,max(case when number = substring(reverse(N),4,1) then thousands else '' end) as thousands
			,max(case when number = substring(reverse(N),3,1) then hundreds else '' end) as hundreds
			,max(case when right(N,2) not between 11 and 19 and number = substring(reverse(N),2,1) then tens else '' end) as tens
			,max(case when right(N,2) not between 11 and 19 and number = right(N,1) then ones 
					 when right(N,2) between 11 and 19 and number = right(N,2) then ones 
					 else '' end) as ones
	from @Numwords 
	cross join dbo.Tally
	group by N
	)

	select N,isnull(thousands+' ','')+isnull(hundreds+' ','')+isnull(tens+' ','')+isnull(ones,'')
	from expanded
	where N <= 9999
set statistics time off;



-- create a temp table to test lookup
create table #numwords (number int primary key, words varchar(200))

;with expanded as 
(select N
		,max(case when number = substring(reverse(N),4,1) then thousands else '' end) as thousands
		,max(case when number = substring(reverse(N),3,1) then hundreds else '' end) as hundreds
		,max(case when right(N,2) not between 11 and 19 and number = substring(reverse(N),2,1) then tens else '' end) as tens
		,max(case when right(N,2) not between 11 and 19 and number = right(N,1) then ones 
				 when right(N,2) between 11 and 19 and number = right(N,2) then ones 
				 else '' end) as ones
from @Numwords 
cross join dbo.Tally
group by N
)
	
insert into #numwords
	select N,isnull(thousands+' ','')+isnull(hundreds+' ','')+isnull(tens+' ','')+isnull(ones,'')
	from expanded
	where N <= 9999

--select * from #numwords


set statistics time on;
-- test using join to existing table
select t.N,nw.words
from dbo.tally t
join #numwords nw on t.N = nw.Number
set statistics time off;


-- test join with set of 9999 random numbers
create table #testSet (testnum int)
insert into #testset
select ABS(CHECKSUM(NEWID())) % 9998 + 1 
from dbo.tally
where N < 10000

set statistics time on;
-- test using join to existing table
select t.testnum,nw.words
from #testSet t
join #numwords nw on t.testNum = nw.Number
set statistics time off;


set statistics time on;
-- test using the user function from the article
select testnum,dbo.fnSpellInteger(testNum)
from #testSet
set statistics time off;


 
drop table #numwords
drop table #testSet