SQL function to convert number to words

Can’t claim credit for this. I got this from SQLTeam.com quite sometime ago. Lost the link.

CREATE  FUNCTION dbo.fn_num_to_words
(    
    @n    bigint
)
RETURNS varchar(1000)
AS
BEGIN
    RETURN
    (
    SELECT    CASE     (@n / 1000000) % 10
                WHEN 1 THEN 'one'
                WHEN 2 THEN 'two'
                WHEN 3 THEN 'three'
                WHEN 4 THEN 'four'
                WHEN 5 THEN 'five'
                WHEN 6 THEN 'six'
                WHEN 7 THEN 'seven'
                WHEN 8 THEN 'eight'
                WHEN 9 THEN 'nine'
                ELSE ''
              END +
        CASE    WHEN (@n / 1000000) % 10 > 0 THEN ' million ' ELSE '' END +
        CASE     (@n / 100000) % 10
                WHEN 1 THEN 'one'
                WHEN 2 THEN 'two'
                WHEN 3 THEN 'three'
                WHEN 4 THEN 'four'
                WHEN 5 THEN 'five'
                WHEN 6 THEN 'six'
                WHEN 7 THEN 'seven'
                WHEN 8 THEN 'eight'
                WHEN 9 THEN 'nine'
                ELSE ''
              END +
        CASE    WHEN (@n / 100000) % 10 > 0 THEN ' hundred' ELSE '' END +
          CASE     WHEN (@n / 1000) % 100 > 0 AND (@n / 1000) > 100 THEN ' AND ' ELSE '' END +
          CASE     (@n / 1000) % 100
                WHEN 10 THEN 'ten'
                WHEN 11 THEN 'eleven'
                WHEN 12 THEN 'twelve'
                WHEN 13 THEN 'thirteen'
                WHEN 14 THEN 'fourteen'
                WHEN 15 THEN 'fifteen'
                WHEN 16 THEN 'sixteen'
                WHEN 17 THEN 'seventeen'
                WHEN 18 THEN 'eighteen'
                WHEN 19 THEN 'nineteen'
                ELSE
                      CASE     (@n / 10000) % 10
                        WHEN 2 THEN 'twen'
                        WHEN 3 THEN 'thir'
                        WHEN 4 THEN 'for'
                        WHEN 5 THEN 'fif'
                        WHEN 6 THEN 'six'
                        WHEN 7 THEN 'seven'
                        WHEN 8 THEN 'eight'
                        WHEN 9 THEN 'nine'
                        ELSE ''
                          END +
                      CASE     WHEN (@n / 10000) % 10 > 0 THEN 'ty' ELSE '' END +
                      CASE     WHEN (@n / 1000) % 10 > 0 AND (@n / 10000) % 10 > 0 THEN ' ' ELSE '' END +
                      CASE     (@n / 1000) % 10
                        WHEN 1 THEN 'one'
                        WHEN 2 THEN 'two'
                        WHEN 3 THEN 'three'
                        WHEN 4 THEN 'four'
                        WHEN 5 THEN 'five'
                        WHEN 6 THEN 'six'
                        WHEN 7 THEN 'seven'
                        WHEN 8 THEN 'eight'
                        WHEN 9 THEN 'nine'
                        ELSE ''
                          END
              END +
          CASE     WHEN (@n / 1000) % 1000 > 0 THEN ' thousand' ELSE '' END +
          CASE     WHEN (@n / 100) % 10 > 0 THEN ' ' ELSE '' END +
          CASE     (@n / 100) % 10
                WHEN 1 THEN 'one'
                WHEN 2 THEN 'two'
                WHEN 3 THEN 'three'
                WHEN 4 THEN 'four'
                WHEN 5 THEN 'five'
                WHEN 6 THEN 'six'
                WHEN 7 THEN 'seven'
                WHEN 8 THEN 'eight'
                WHEN 9 THEN 'nine'
                ELSE ''
              END +
          CASE     WHEN (@n / 100) % 10 > 0 THEN ' hundred' ELSE '' END +
          CASE     WHEN @n % 100 > 0 AND @n > 100 THEN ' AND ' ELSE '' END +
          CASE     @n % 100
                WHEN 10 THEN 'ten'
                WHEN 11 THEN 'eleven'
                WHEN 12 THEN 'twelve'
                WHEN 13 THEN 'thirteen'
                WHEN 14 THEN 'fourteen'
                WHEN 15 THEN 'fifteen'
                WHEN 16 THEN 'sixteen'
                WHEN 17 THEN 'seventeen'
                WHEN 18 THEN 'eighteen'
                WHEN 19 THEN 'nineteen'
                ELSE
                      CASE     (@n / 10) % 10
                        WHEN 2 THEN 'twen'
                        WHEN 3 THEN 'thir'
                        WHEN 4 THEN 'for'
                        WHEN 5 THEN 'fif'
                        WHEN 6 THEN 'six'
                        WHEN 7 THEN 'seven'
                        WHEN 8 THEN 'eight'
                        WHEN 9 THEN 'nine'
                        ELSE ''
                          END +
                      CASE     WHEN (@n / 10) % 10 > 0 THEN 'ty' ELSE '' END +
                      CASE     WHEN @n % 10 > 0 AND (@n / 10) % 10 > 0 THEN ' ' ELSE '' END +
                      CASE     @n % 10
                        WHEN 1 THEN 'one'
                        WHEN 2 THEN 'two'
                        WHEN 3 THEN 'three'
                        WHEN 4 THEN 'four'
                        WHEN 5 THEN 'five'
                        WHEN 6 THEN 'six'
                        WHEN 7 THEN 'seven'
                        WHEN 8 THEN 'eight'
                        WHEN 9 THEN 'nine'
                        ELSE ''
                          END
            END words
    )
END
Advertisements

About msarm

Aspiring Enterprise Architect.
This entry was posted in SQL Server 2005/2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s