September 25, 2009 at 9:31 pm
Hi
I want to convert number into words in sql server 2005.For examople,we enter number like 15000 from front end ,but it should be save into database like "fifteen thousand:".
Please guide me how it will be saved!
Thanks
September 26, 2009 at 8:45 am
I have not had the opportunity to test these, all of which were posted by individuals to the SQL Server Central site. Review them, test them and say a quiet thank you to those generous people who posted them. Keep in mind that it is generally faster to have your front end do the conversion and not T-SQ"L
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udfn_NumberToWords]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'/*----------------------------------------------------------------------------------------------------------------------
Name: ctfn_NumberToWords
Version: 1.0
Date: 21-Feb-2007
Description: Takes an integer and converts it to words by storing the
integer into a table three digits at a time as hundreds, tens
and a factorial multiplier ''units''.
Returns a VARCHAR(2000) string containing the text of the
number, e.g.: ONE THOUSAND ONE HUNDRED AND TWENTY FOUR
Returns MINUS for a minus number and NULL if there is an error
Usage: SELECT dbo.ctfn_NumberToWords(INTEGER)
SELECT dbo.ctfn_NumberToWords(1787.55) + '' DOLLARS '' + dbo.ctfn_NumberToWords((1787.55 - CAST(1787.55 AS INT)) * 100) + '' CENTS.''
gives
ONE THOUSAND SEVEN HUNDRED AND EIGHTY SEVEN DOLLARS FIFTY FIVE CENTS.
David le Quesne
----------------------------------------------------------------------------------------------------------------------
History
1.0 DLQ 21-02-2007 RFC001428 Function created for ATPs
----------------------------------------------------------------------------------------------------------------------*/
CREATE FUNCTION [dbo].[udfn_NumberToWords] (@intNumberValue INTEGER)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @strNumberString VARCHAR(9)
DECLARE @strReturn VARCHAR(2000)
DECLARE @intUnits SMALLINT
-- Create table of number groups
DECLARE @tblNumberGroups TABLE (Units SMALLINT, Hundreds SMALLINT, Tens SMALLINT)
-- Handle errors and ''quick wins''
IF @intNumberValue IS NULL RETURN NULL
IF ISNUMERIC(@intNumberValue)=0 RETURN NULL
IF @intNumberValue = 0 RETURN ''ZERO''
IF @intNumberValue 0 OR (@intNumberValue/1000) >0
BEGIN
INSERT INTO @tblNumberGroups (Units, Hundreds, Tens) VALUES (@intUnits, (@intNumberValue % 1000)/100, (@intNumberValue % 1000) % 100 )
SELECT @intNumberValue = CAST (@intNumberValue / 1000 AS INTEGER)
SET @intUnits = @intUnits + 1
END
-- Remove last unit added
SET @intUnits = @intUnits-1
-- Concatenate text number by reading number groups in reverse order
SELECT @strReturn = ISNULL(@strReturn,'' '') +
ISNULL(
ISNULL((CASE Hundreds
WHEN 1 THEN ''ONE HUNDRED ''
WHEN 2 THEN ''TWO HUNDRED ''
WHEN 3 THEN ''THREE HUNDRED ''
WHEN 4 THEN ''FOUR HUNDRED ''
WHEN 5 THEN ''FIVE HUNDRED ''
WHEN 6 THEN ''SIX HUNDRED ''
WHEN 7 THEN ''SEVEN HUNDRED ''
WHEN 8 THEN ''EIGHT HUNDRED ''
WHEN 9 THEN ''NINE HUNDRED ''
END),'' '') +
CASE WHEN (Hundreds >0 OR Units 0 THEN '' AND '' ELSE '' '' END +
ISNULL((CASE Tens / 10
WHEN 2 THEN ''TWENTY ''
WHEN 3 THEN ''THIRTY ''
WHEN 4 THEN ''FORTY ''
WHEN 5 THEN ''FIFTY ''
WHEN 6 THEN ''SIXTY ''
WHEN 7 THEN ''SEVENTY ''
WHEN 8 THEN ''EIGHTY ''
WHEN 9 THEN ''NINETY ''
END),'' '') +
ISNULL((CASE Tens
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 ''
END),'' '') +
COALESCE(
CASE WHEN Tens %10 =1 AND Tens / 10 1 THEN ''ONE '' END,
CASE WHEN Tens %10 =2 AND Tens / 10 1 THEN ''TWO '' END,
CASE WHEN Tens %10 =3 AND Tens / 10 1 THEN ''THREE '' END,
CASE WHEN Tens %10 =4 AND Tens / 10 1 THEN ''FOUR '' END,
CASE WHEN Tens %10 =5 AND Tens / 10 1 THEN ''FIVE '' END,
CASE WHEN Tens %10 =6 AND Tens / 10 1 THEN ''SIX '' END,
CASE WHEN Tens %10 =7 AND Tens / 10 1 THEN ''SEVEN '' END,
CASE WHEN Tens %10 =8 AND Tens / 10 1 THEN ''EIGHT '' END,
CASE WHEN Tens %10 =9 AND Tens / 10 1 THEN ''NINE '' END,
'' '')+
COALESCE(
CASE WHEN Units=1 AND (Hundreds>0 OR Tens>0) THEN ''THOUSAND '' END,
CASE WHEN Units=2 AND (Hundreds>0 OR Tens>0) THEN ''MILLION '' END,
CASE WHEN Units=3 AND (Hundreds>0 OR Tens>0) THEN ''BILLION '' END,
CASE WHEN Units=4 AND (Hundreds>0 OR Tens>0) THEN ''TRILLION '' END,
'' '')
,'' '')
FROM @tblNumberGroups
ORDER BY units DESC
-- Get rid of all the spaces
WHILE CHARINDEX('' '', @strReturn)>0
BEGIN
SET @strReturn = REPLACE(@strReturn,'' '','' '')
END
SET @strReturn = LTRIM(RTRIM(@strReturn))
RETURN @strReturn
END
'
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnSpellNumber]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[fnSpellNumber] ( @number varchar(20) )
RETURNS VARCHAR(200)
AS
--For debugging: declare @number varchar(20) set @number = ''192.1''
BEGIN
-- This is for use outside of a function: DECLARE @debug bit SET @debug = 0
DECLARE @result varchar(200), @word varchar(100)
DECLARE @i int, @intpart varchar(20), @decpart varchar(20)
SET @word = LTRIM(RTRIM(@number))
-- Check for a bad number, e.g., one with embedded spaces
IF ISNUMERIC(@word) = 0 RETURN ''<>''
SET @i = CHARINDEX(''.'', @word)
-- Remove trailing zeroes for any decimal portion
IF @i > 0 -- Number contains a decimal point
BEGIN
WHILE RIGHT(@word,1) = ''0'' SET @word = LEFT(@word,LEN(@word)-1)
IF @word = '''' SET @word = ''0''
END
-- Insert a decimal point at the end if none was specified
IF @i = 0 -- No decimal point
BEGIN
SET @word = @number + ''.''
SET @i = CHARINDEX(''.'', @word)
END
SET @intpart = LEFT(@word,@i-1)-- Extract the integer part of the number if any
IF LEN(@intpart) > 0
SET @result = master.dbo.fnSpellInteger(CAST(@intpart AS int))
ELSE
SET @result = ''''
-- Extract the decimal portion of the number
SET @decpart = RIGHT(@word,LEN(@word)-@i)-- @i is position of decimal point
IF LEN(@decpart) > 0
BEGIN
IF @result = ''Zero''
SET @result = ''''
ELSE IF @result ''''
SET @result = @result + '' and ''
SET @result = @result + master.dbo.fnSpellInteger(@decpart) +
CASE LEN(@decpart)
WHEN 0 THEN ''''
WHEN 1 THEN '' Tenths''
WHEN 2 THEN '' Hundredths''
WHEN 3 THEN '' One-Thousandths''
WHEN 4 THEN '' Ten-Thousandths''
WHEN 5 THEN '' One-Hundred-Thousandths''
WHEN 6 THEN '' One-Millionths''
WHEN 7 THEN '' Ten-Millionths''
WHEN 8 THEN '' One-Hundred-Millionths''
WHEN 9 THEN '' One-Billionths''
END
-- Check for a valid plural
IF @decpart = 1 SET @result = LEFT(@result, LEN(@result)-1)-- Remove last "s" for just 1
END
-- This is for use outside of a function: if @debug = 1 select @word as ''@word'', @i as ''@i'', @intpart as ''@intpart'', @decpart as ''@decpart'', @result as ''@result''
RETURN @result
END
'
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnSpellInteger]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[fnSpellInteger] ( @number int )
RETURNS VARCHAR(100)
AS
BEGIN
-- For debugging outside of the UDF: DECLARE @debug bit SET @debug = 0
DECLARE @result VARCHAR(100), @word VARCHAR(100), @group VARCHAR(100)
DECLARE @i int, @j-2 int, @m int, @digit VARCHAR(2), @cn VARCHAR(20)
IF @number = 0 RETURN ''Zero''
SELECT @result = '''', @word = '''', @group = ''''
SET @cn = @number
SET @cn = REPLACE(@cn,'','','''')
SET @m = LEN(@cn) % 3
IF @m > 0 SET @cn = REPLICATE(''0'',3-@m) + @cn-- Left pad with zeroes to a multiple of 3
SET @i = 1
SET @j-2 = LEN(@cn)-@i+1
SET @m = @i % 3
WHILE @i <= LEN(@cn)
BEGIN
-- @i is 1 origin index into numeric string while @m = @i modulo 3
-- If the middle digit of each group of 3 is a ''1'' then this is a ''Ten'' or a ''...teen''
IF @m = 2 AND SUBSTRING(@cn,@i,1) = ''1''
BEGIN
SET @digit = SUBSTRING(@cn,@i,2)
-- Skip rightmost digit of 3 if processing teens
SET @i = @i + 1
END
ELSE
SET @digit = SUBSTRING(@cn,@i,1)
SET @word =
CASE
WHEN @m = 0 THEN-- Rightmost digit of group of 3
CASE @digit
WHEN ''0'' THEN ''''
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''
END +
CASE
WHEN (@group '''' OR @digit ''0'') AND (@j+2) / 3 = 2 THEN '' Thousand''
WHEN (@group '''' OR @digit ''0'') AND (@j+2) / 3 = 3 THEN '' Million''
WHEN (@group '''' OR @digit ''0'') AND (@j+2) / 3 = 4 THEN '' Billion''
ELSE ''''
END
WHEN LEN(@digit) = 2 THEN-- Special case when middle digit is a ''1''
CASE @digit
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''
END +
CASE
WHEN (@group '''' OR @digit ''00'') AND (@j+2) / 3 = 2 THEN '' Thousand''
WHEN (@group '''' OR @digit ''00'') AND (@j+2) / 3 = 3 THEN '' Million''
WHEN (@group '''' OR @digit ''00'') AND (@j+2) / 3 = 4 THEN '' Billion''
ELSE ''''
END
WHEN @m = 2 THEN-- Middle digit of group of 3
CASE @digit
WHEN ''2'' THEN ''Twenty''
WHEN ''3'' THEN ''Thirty''
WHEN ''4'' THEN ''Forty''
WHEN ''5'' THEN ''Fifty''
WHEN ''6'' THEN ''Sixty''
WHEN ''7'' THEN ''Seventy''
WHEN ''8'' THEN ''Eighty''
WHEN ''9'' THEN ''Ninety''
ELSE ''''
END
WHEN @m = 1 THEN-- Leftmost digit of group of 3
CASE @digit
WHEN ''0'' THEN ''''
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''
END +
CASE WHEN @digit ''0'' THEN '' Hundred'' ELSE '''' END
END
SET @group = @group + RTRIM(@word)-- Group value
IF @word ''''
BEGIN
DECLARE @prefix VARCHAR(20)
IF CHARINDEX('' '',@word) > 0 SET @prefix = LEFT(@word,CHARINDEX('' '',@word)) ELSE SET @prefix = @word
IF RIGHT(@result,2) = ''ty'' AND @prefix IN (''One'',''Two'',''Three'',''Four'',''Five'',''Six'',''Seven'',''Eight'',''Nine'')
SET @result = @result + ''-'' + LTRIM(@word)
ELSE
SET @result = @result + '' '' + LTRIM(@word)
END
-- The following needs to be outside of a UDF to work:
--IF @debug = 1 SELECT @cn as ''Number'', @i as ''@i'', @j-2 as ''@j'', @m as ''@m'', @digit as ''@digit'', CAST(replace(@group,'' '',''`'') AS CHAR(30)) as ''@group'', @word as ''@word'', @result as ''@result''
SET @i = @i + 1
SET @j-2 = LEN(@cn)-@i+1
SET @m = @i % 3
IF @m = 1 SET @group = ''''-- Clear group value when starting a new one
END
IF @result = '''' SET @result = ''0''
RETURN LTRIM(@Result)
END
'
END
September 26, 2009 at 11:19 am
raaz66 (9/25/2009)
HiI want to convert number into words in sql server 2005.For examople,we enter number like 15000 from front end ,but it should be save into database like "fifteen thousand:".
Please guide me how it will be saved!
Thanks
As BitBucket pointed out, it is normally better (for many reasons) to do this type of thing in the GUI. That, not withstanding, I have to ask, why do you need to do this? It may help determine what the best solution is.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2012 at 12:18 am
I'm not sure why doing it on the front-end would be 'better'. By doing it in SQL it remains accessible and usable from ANY front-end, report writing, views etc.... it is an extremely simple statement and doesn't require any database access - so the only performance overhead would be in the comms between front-end and DB - but once written it can be used anywhere. We apply this same logic to most business rules and functionality. As a result of this approach we were able to convert an 12 large client server style systems to ASP based web pages in 8 weeks with just one programming resource.... not trying to sing our praises, rather just trying to re-emphasis this forums name 'SQL ServerCentral' wherein the database is 'Central' to our development and programming efforts.
June 29, 2012 at 7:24 pm
craig-962225 (6/29/2012)
...it is an extremely simple statement and doesn't require any database access...
Would you mind sharing it then?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 4, 2012 at 6:30 am
... As a result of this approach we were able to convert an 12 large client server style systems....
Large? May be. But definitely not an international ones. Otherwise you would need to convert and save into database "number in words" in American English, British English and Indian English (have you ever heard about Lakh?)
😉
July 4, 2012 at 6:37 am
I would suggest creating and using CLR function. The base for it you can find here:
http://www.c-sharpcorner.com/uploadfile/b942f9/converting-numbers-to-words-in-C-Sharp/
You can enhance it to support any numbering style. Also, it's possible to reuse it in UI or/and server side code.
I cannot say that you shouldn't save words into database, but I would do it if database designed for reporting purposes only.
July 4, 2012 at 6:46 am
I don't have access to the function right now but will try to post it later... When I say simple I really mean the call is simple. We handled it by using and adapting some code posted on another similar forum - it essentially creates three functions that together create a very flexible conversion. Being new to posting onto these kinds of forums I am not sure on the protocol and best way to upload code snippets - so if someone could enlighten me it would be great and I'll upload the functions. BTW - my posting was aimed more at the general assumption that it is better to do it on the front-end - which we have encountered regularly and have continually tried to dis-prove. Doing it with raw native SQL provides the maximum portability.
July 4, 2012 at 7:08 am
... Doing it with raw native SQL provides the maximum portability.
Agree, but that is impossible with "raw native SQL" at the moment as there is no such "raw native" in-build function in SQL.
Lets call MS to extend their FORMAT function in SQL2012...
Again, it'd depend what you call by "portability". I'm sure if you would need to port your system to different English, you would need to convert your words again.
Let say 1,000,000,000:
In short scale (American English) it's called billion (widely used in finance and other fields), but in Long Scale (many European languages) it's called as milliard or just thousand million. As traditionally the billion is used for one million, million (1,000,000,000,000), which is called as trillion in US (short scale).
There are plenty more differences in wording of numbers eg. using "AND" between some fractions...
So, I cannot see how you can achieve good portability by saving it in database. I would think the FORMAT based on locale would be the best choice.
July 4, 2012 at 7:18 am
Fair enough - in the case of number to word using built in functionality is probably preferable .... this was a very specific thread and my response was more 'generic'.... In our case we have stored all business rules, conversions, validations etc in SQL code. By doing this when porting to a new device / environment we only needed to write controller code for that specific environment and new that without doubt our existing business rules will work.... for us it has certainly made it a lot easier to convert all the apps we had previously built as Delphi Client Server apps to ASP (first) and then to PHP or .NET later.... Whenever we tell anyone that all our rules and business logic is written in SQL code they respond as if we are insane... then I just smile when I watch them try to convert an existing Application with business rules in multiple (or even single) front-end or middle-tier languages. Our contention is that SQL has been relatively unchanged for the past 30 year and in nearly all cases changes have been backward compatible - but front end languages come and go.
July 4, 2012 at 9:08 am
craig-962225 (7/4/2012)
I don't have access to the function right now but will try to post it later... When I say simple I really mean the call is simple. We handled it by using and adapting some code posted on another similar forum - it essentially creates three functions that together create a very flexible conversion. Being new to posting onto these kinds of forums I am not sure on the protocol and best way to upload code snippets - so if someone could enlighten me it would be great and I'll upload the functions. BTW - my posting was aimed more at the general assumption that it is better to do it on the front-end - which we have encountered regularly and have continually tried to dis-prove. Doing it with raw native SQL provides the maximum portability.
Looking forward to it. I've seen a lot of different methods, most of which are relatively terrible for perforance so I'm looking forward to a good one.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 4, 2012 at 9:22 am
Jeff Moden (7/4/2012)
craig-962225 (7/4/2012)
I don't have access to the function right now but will try to post it later... When I say simple I really mean the call is simple. We handled it by using and adapting some code posted on another similar forum - it essentially creates three functions that together create a very flexible conversion. Being new to posting onto these kinds of forums I am not sure on the protocol and best way to upload code snippets - so if someone could enlighten me it would be great and I'll upload the functions. BTW - my posting was aimed more at the general assumption that it is better to do it on the front-end - which we have encountered regularly and have continually tried to dis-prove. Doing it with raw native SQL provides the maximum portability.Looking forward to it. I've seen a lot of different methods, most of which are relatively terrible for perforance so I'm looking forward to a good one.
Yeah, as it will need to make quite a few string manipulations, I would think that CLR more appropriate for this sort of things also from performance point of view...
I guess, for small scale solutions T-SQL will be fine (eg. implementing all business rules and validations in database as per Craig last post). For something more scalable? There is no even point in spending time for the discussion here...
August 8, 2012 at 7:40 pm
Jeff Moden (7/4/2012)
craig-962225 (7/4/2012)
I don't have access to the function right now but will try to post it later... When I say simple I really mean the call is simple. We handled it by using and adapting some code posted on another similar forum - it essentially creates three functions that together create a very flexible conversion. Being new to posting onto these kinds of forums I am not sure on the protocol and best way to upload code snippets - so if someone could enlighten me it would be great and I'll upload the functions. BTW - my posting was aimed more at the general assumption that it is better to do it on the front-end - which we have encountered regularly and have continually tried to dis-prove. Doing it with raw native SQL provides the maximum portability.Looking forward to it. I've seen a lot of different methods, most of which are relatively terrible for perforance so I'm looking forward to a good one.
Still looking forward to this. Just include the code between the "IFCode" [ code="sql" ]put code here[ /code ] without any spaces between the brackets.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 27, 2016 at 1:27 pm
Hello,
I wonder if there was any change since 2009 when this subject was raised and the solution - provided.
I have a process, building xml of invoices (hence I cannot use client side, as someone has asked in this chain)
and one of the customers, receiving those invoices now requires also to add the words, so invoice for £22.55 will have <sum_in_words>twenty two period fifty five</sum_in_words>
As the system is multi-currency, I would t rather to avoid the currency, unless I want to maintain currency names table, e.g. RUB Russian Ruble Ruble Kopeika
Many thanks!
January 27, 2016 at 3:15 pm
At this time there is no built-in functionality in SQL Server for transforming a number into words.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply