convert number into words

  • 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

  • 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

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • raaz66 (9/25/2009)


    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

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ... 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?)

    😉

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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.

  • ... 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.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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.

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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!

  • At this time there is no built-in functionality in SQL Server for transforming a number into words.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply