Skip to main content

Convert Number To Words in SQL Sever

Ref: http://www.novicksoftware.com/udfofweek/Vol2/T-SQL-UDF-Vol-2-Num-9-udf_Num_ToWords.htm


SET QUOTED_IDENTIFIER ON


SET ANSI_NULLS ON

SET NOCOUNT ON

GO

CREATE FUNCTION dbo.NumToWords (



@Number Numeric (38, 0) -- Input number with as many as 18 digits



) RETURNS VARCHAR(8000)

AS BEGIN

DECLARE @inputNumber VARCHAR(38)

DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))

DECLARE @outputString VARCHAR(8000)

DECLARE @length INT

DECLARE @counter INT

DECLARE @loops INT

DECLARE @position INT

DECLARE @chunk CHAR(3) -- for chunks of 3 numbers

DECLARE @tensones CHAR(2)

DECLARE @hundreds CHAR(1)

DECLARE @tens CHAR(1)

DECLARE @ones CHAR(1)

IF @Number = 0 Return 'Zero'

-- initialize the variables

SELECT @inputNumber = CONVERT(varchar(38), @Number)

, @outputString = ''

, @counter = 1

SELECT @length = LEN(@inputNumber)

, @position = LEN(@inputNumber) - 2

, @loops = LEN(@inputNumber)/3

-- make sure there is an extra loop added for the remaining numbers

IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1

-- insert data for the numbers and words

INSERT INTO @NumbersTable SELECT '00', ''

UNION ALL SELECT '01', 'one' UNION ALL SELECT '02', 'two'

UNION ALL SELECT '03', 'three' UNION ALL SELECT '04', 'four'

UNION ALL SELECT '05', 'five' UNION ALL SELECT '06', 'six'

UNION ALL SELECT '07', 'seven' UNION ALL SELECT '08', 'eight'

UNION ALL SELECT '09', '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 '20', 'twenty'

UNION ALL SELECT '30', 'thirty' UNION ALL SELECT '40', 'forty'

UNION ALL SELECT '50', 'fifty' UNION ALL SELECT '60', 'sixty'

UNION ALL SELECT '70', 'seventy' UNION ALL SELECT '80', 'eighty'

UNION ALL SELECT '90', 'ninety'

WHILE @counter <= @loops BEGIN

-- get chunks of 3 numbers at a time, padded with leading zeros

SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)

IF @chunk <> '000' BEGIN

SELECT @tensones = SUBSTRING(@chunk, 2, 2)

, @hundreds = SUBSTRING(@chunk, 1, 1)

, @tens = SUBSTRING(@chunk, 2, 1)

, @ones = SUBSTRING(@chunk, 3, 1)

-- If twenty or less, use the word directly from @NumbersTable

IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN

SET @outputString = (SELECT word

FROM @NumbersTable

WHERE @tensones = number)

+ CASE @counter WHEN 1 THEN '' -- No name

WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '

WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '

WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '

WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '

WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '

WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '

ELSE '' END

+ @outputString

END

ELSE BEGIN -- break down the ones and the tens separately

SET @outputString = ' '

+ (SELECT word

FROM @NumbersTable

WHERE @tens + '0' = number)

+ '-'

+ (SELECT word

FROM @NumbersTable

WHERE '0'+ @ones = number)

+ CASE @counter WHEN 1 THEN '' -- No name

WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '

WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '

WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '

WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '

WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '

WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '

ELSE '' END

+ @outputString

END

-- now get the hundreds

IF @hundreds <> '0' BEGIN

SET @outputString = (SELECT word

FROM @NumbersTable

WHERE '0' + @hundreds = number)

+ ' hundred '

+ @outputString

END

END

SELECT @counter = @counter + 1

, @position = @position - 3

END

-- Remove any double spaces

SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, ' ', ' ')))

SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)

RETURN @outputString -- return the result

END

GO

GRANT EXEC on dbo.NumToWords TO PUBLIC

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Comments

  1. I do believe all of the ideas you've offered for your post.
    They are really convincing and can definitely work.
    Nonetheless, the posts are very short for newbies. May just
    you please extend them a bit from subsequent time? Thanks for the post.


    My blog post; 98125 new construction homes for sale

    ReplyDelete

Post a Comment

Popular posts from this blog

Crystal reports load report failed: Could not load file or assembly CrystalDecisions.Web, Version=10.2.3600.0

Recently we upgraded our Web Application Server to Windows Server 2008. After this, crystal reports in the VS2005 applications failed to load. This is because VS 2005 applications were using crystal report version 10.2.3600.0 which we can't install in Windows Server 2008. In the new server we can only install the crystal report version 10.5.3700.0. I had to follow the below steps to resolve this issue. Downloaded and installed new version of crystal report from the page below. http://wiki.sdn.sap.com/wiki/pages/viewpage.action?pageId=56787567 Changed versions(10.2.3600.0 to 10.5.3700.0) in Web.Config and report viewer pages We removed all the crystal report reference from the project and tried to refer the new versions. But new versions were not displayed in the list. So I decided to take a local copy of these dlls and refer them. To do this run the below command( as Admin ) regsvr32 -u C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\shfusion.dll The above command will help you

ഗുരു ദേവോ ഭവ:

ഇ ന്ന് ദേശീയ അധ്യാപക ദിനം. അധ്യാപകനും രാഷ്ട്രപതിയുമായിരുന്ന ഡോ.എസ് രാധാകൃഷ്ണന്‍റെ പിറന്നാള്‍ ദിനമാണ് അധ്യാപക ദിനമായി നാം ആചരിക്കുന്നത്. ഈ ഒരു സുദിനത്തില്‍ ഈയൊരു അധ്യാപകനെ സ്മരിച്ചില്ലെങ്കില്‍ അതൊരു നന്ദികേടായി പോവും.  ഓര്‍മ്മക്കുറിപ്പിലേക്ക് കടക്കുന്നതിനു മുന്‍പൊരു ഡിസ്ക്ലെയിമര്‍. ബാദ്ധ്യതാ നിരാകരണം ഒടുക്കം മാത്രം നടത്തുന്നതാണ് ആചാരം. എങ്കിലും, അതു താനല്ലയോ ഇതെന്ന് ചുമ്മാ കല്‍പ്പിച്ചു കൂട്ടി എന്തിനുമേതിനും വ്രണപ്പെടുന്നൊരു സമൂഹത്തില്‍, അവകാശപരിത്യാഗം ആദ്യം തന്നെ നടത്തുകയെന്ന ആചാരലംഘനമാവും ഉചിതം. അതെ, ഇനി ഇവിടെ പറയാന്‍ പോവുന്നതൊരു മിത്ത് മാത്രമാണു. എന്ന്? എവിടെ? എത്രത്തോളം? നടന്നുവെന്നതിനൊന്നും ഇവിടെയൊരു പ്രസക്തിയില്ല. പതിറ്റാണ്ടുകളായി കേരളത്തിലെ പലപല കോളേജുകളും ഇതെന്‍റെ ഗര്‍ഭമാണെന്ന അവകാശവാദവുമായി എത്തിയിട്ടുണ്ടു. സത്യം ആര്‍ക്കറിയാം! ഒരു കാര്യം മാത്രം എനിക്കു തറപ്പിച്ചു പറയാം, എന്‍റെ ഗര്‍ഭം ഇങ്ങിനെയല്ല. അതുകൊണ്ടു തന്നെ ഈ കഥയിലെ കഥാപാത്രങ്ങളും കഥാപരിസരവും സാങ്കല്‍പ്പികം മാത്രമാണു, മറിച്ച് തോന്നുന്നെങ്കില്‍ അതു കയ്യിലിരിപ്പിന്‍റെ ഗുണം

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I was getting timeout error while executing a stored procedure from the web application. But this stored procedure is executing within a second while running from SQL management studio. This issue can happen when database's statistics and/query plan cache are incorrect. This can be resolved by updating statistics by executing  exec sp_updatestats Error: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. Solution:  exec sp_updatestats If issue didn't resolve even after executing above this, you may need to optimize the query.