Create a stored proc. for converting currency to Words

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Create a stored proc. for converting currency to Words

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-05 : 08:46:05

Mangesh writes "Could you please help me out in writing an SQL stored procedure that accepts a currency value as an input parameter and then returns an output that converts its actual value in Words.

e.g. If the user supplies $2000 as a parameter then the procedure should return (Dollars Two Thousand only) as the output.

Kindly provide some solution as I really don't know how to go about it.

Thanks."

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-05 : 14:58:01

No, but here's a select (good for up to 999999)


SELECT
  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
FROM YourTable

You may want to remove the 'and's depending on where it's for.

mdaslam81
Starting Member

2 Posts

Posted - 2006-04-03 : 04:09:49

Thanks ALL Great job,

sunilmahanti
Starting Member

4 Posts

Posted - 2011-01-06 : 00:15:51

this code only display upto 9lakhs but i need more then 10 lakhs ,plz help for the complete code upto 1cr

thankx 4 adv

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources