StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Consider using an auxiliary numbers table.</p> <p><strong>NB: This MS SQL</strong></p> <p>Create a Sequence table - This could include all the numbers you need or at least up to 999. I have limited it to the least but it adds extra logic.</p> <pre><code>CREATE TABLE [dbo].[Sequence] ( seq INTEGER NOT NULL UNIQUE, word [varchar](25) NOT NULL ) INSERT INTO [Sequence] SELECT 0, '' INSERT INTO [Sequence] SELECT 1, 'One' INSERT INTO [Sequence] SELECT 2, 'Two' INSERT INTO [Sequence] SELECT 3, 'Three' INSERT INTO [Sequence] SELECT 4, 'Four' INSERT INTO [Sequence] SELECT 5, 'Five' INSERT INTO [Sequence] SELECT 6, 'Six' INSERT INTO [Sequence] SELECT 7, 'Seven' INSERT INTO [Sequence] SELECT 8, 'Eight' INSERT INTO [Sequence] SELECT 9, 'Nine' INSERT INTO [Sequence] SELECT 10, 'Ten' INSERT INTO [Sequence] SELECT 11, 'Eleven' INSERT INTO [Sequence] SELECT 12, 'Twelve' INSERT INTO [Sequence] SELECT 13, 'Thirteen' INSERT INTO [Sequence] SELECT 14, 'Fourteen' INSERT INTO [Sequence] SELECT 15, 'Fifteen' INSERT INTO [Sequence] SELECT 16, 'Sixteen' INSERT INTO [Sequence] SELECT 17, 'Seventeen' INSERT INTO [Sequence] SELECT 18, 'Eighteen' INSERT INTO [Sequence] SELECT 19, 'Nineteen' INSERT INTO [Sequence] SELECT 20, 'Twenty' INSERT INTO [Sequence] SELECT 30, 'Thirty' INSERT INTO [Sequence] SELECT 40, 'Forty' INSERT INTO [Sequence] SELECT 50, 'Fifty' INSERT INTO [Sequence] SELECT 60, 'Sixty' INSERT INTO [Sequence] SELECT 70, 'Seventy' INSERT INTO [Sequence] SELECT 80, 'Eighty' INSERT INTO [Sequence] SELECT 90, 'Ninty' </code></pre> <p>Then Create the user defined function.</p> <pre><code>CREATE FUNCTION dbo.udf_NumToWords ( @num AS INTEGER ) RETURNS VARCHAR(50) AS BEGIN DECLARE @words AS VARCHAR(50) IF @num = 0 SELECT @words = 'Zero' ELSE IF @num < 20 SELECT @words = word FROM sequence WHERE seq = @num ELSE IF @num < 100 (SELECT @words = TTens.word + ' ' + TUnits.word FROM Sequence AS TUnits CROSS JOIN Sequence AS TTens WHERE TUnits.seq = (@num % 100) % 10 AND TTens.seq = (@num % 100) - (@num % 100) % 10 ) ELSE IF @num = 100 (SELECT @words = THundreds.word + ' Hundred' FROM Sequence AS THundreds WHERE THundreds.seq = (@num / 100) ) ELSE IF @num < 1000 ( SELECT @words = THundreds.word + ' Hundred and ' + TTens.word + ' ' + TUnits.word FROM Sequence AS TUnits CROSS JOIN Sequence AS TTens CROSS JOIN Sequence AS THundreds WHERE TUnits.seq = (@num % 100) % 10 AND TTens.seq = (@num % 100) - (@num % 100) % 10 AND THundreds.seq = (@num / 100) ) ELSE IF @num = 1000 (SELECT @words = TThousand.word + ' Thousand' FROM Sequence AS TThousand WHERE TThousand.seq = (@num / 1000) ) ELSE IF @num < 10000 ( SELECT @words = TThousand.word + ' Thousand ' + THundreds.word + ' Hundred and ' + TTens.word + ' ' + TUnits.word FROM Sequence AS TUnits CROSS JOIN Sequence AS TTens CROSS JOIN Sequence AS THundreds CROSS JOIN Sequence AS TThousand WHERE TUnits.seq = (@num % 100) % 10 AND TTens.seq = (@num % 100) - (@num % 100) % 10 AND THundreds.seq = (@num / 100) - (@num / 1000) * 10 AND TThousand.seq = (@num / 1000) ) ELSE SELECT @words = STR(@num) RETURN @words END </code></pre> <p>Now Test Function:</p> <pre><code>SELECT NumberAsWords = dbo.udf_NumToWords(888); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload