Number expressed as words

  • Thomas Rushton

    SSC-Insane

    Points: 22648

    We need to express an integer (eg 1034) as words ("one thousand and thirty four").

    Does anyone here know of anything that'll do this conversion for us, or are we going to have to figure it out for ourselves?

    Thanks!

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • This was removed by the editor as SPAM

  • Antares686

    SSC Guru

    Points: 125444

    This is how I do it with Money values to word. WIth a few changes you should have exactly what you need.

    http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=259&CategoryNm=T-SQL%20Aids&CategoryID=19

    -----------------Code-------------------

    --You will need this table.--

    CREATE TABLE NumNameTbl (

    [val] [int] NOT NULL,

    [numname] [varchar](20) NOT NULL,

    CONSTRAINT [PK_NumNameTbl_val] PRIMARY KEY CLUSTERED

    (

    [val]

    ) WITH FILLFACTOR = 100

    )

    GO

    /* Insert the value records, yes this is a heap table */

    INSERT INTO NumNameTbl (val,numname) VALUES (1,'One')

    INSERT INTO NumNameTbl (val,numname) VALUES (2,'Two')

    INSERT INTO NumNameTbl (val,numname) VALUES (3,'Three')

    INSERT INTO NumNameTbl (val,numname) VALUES (4,'Four')

    INSERT INTO NumNameTbl (val,numname) VALUES (5,'Five')

    INSERT INTO NumNameTbl (val,numname) VALUES (6,'Six')

    INSERT INTO NumNameTbl (val,numname) VALUES (7,'Seven')

    INSERT INTO NumNameTbl (val,numname) VALUES (8,'Eight')

    INSERT INTO NumNameTbl (val,numname) VALUES (9,'Nine')

    INSERT INTO NumNameTbl (val,numname) VALUES (10,'Ten')

    INSERT INTO NumNameTbl (val,numname) VALUES (11,'Eleven')

    INSERT INTO NumNameTbl (val,numname) VALUES (12,'Twelve')

    INSERT INTO NumNameTbl (val,numname) VALUES (13,'Thirteen')

    INSERT INTO NumNameTbl (val,numname) VALUES (14,'Fourteen')

    INSERT INTO NumNameTbl (val,numname) VALUES (15,'Fifteen')

    INSERT INTO NumNameTbl (val,numname) VALUES (16,'Sixteen')

    INSERT INTO NumNameTbl (val,numname) VALUES (17,'Seventeen')

    INSERT INTO NumNameTbl (val,numname) VALUES (18,'Eightteen')

    INSERT INTO NumNameTbl (val,numname) VALUES (19,'Nineteen')

    INSERT INTO NumNameTbl (val,numname) VALUES (20,'Twenty')

    INSERT INTO NumNameTbl (val,numname) VALUES (30,'Tirty')

    INSERT INTO NumNameTbl (val,numname) VALUES (40,'Fourty')

    INSERT INTO NumNameTbl (val,numname) VALUES (50,'Fifty')

    INSERT INTO NumNameTbl (val,numname) VALUES (60,'Sixty')

    INSERT INTO NumNameTbl (val,numname) VALUES (70,'Seventy')

    INSERT INTO NumNameTbl (val,numname) VALUES (80,'Eighty')

    INSERT INTO NumNameTbl (val,numname) VALUES (90,'Ninty')

    GO

    /* The procedure. */

    CREATE PROCEDURE ip_ReturnMoneyName

    @Money Money

    AS

    SET NOCOUNT ON

    DECLARE @MonName VARCHAR(8000)

    DECLARE @MonStr VARCHAR(100)

    DECLARE @TempMon VARCHAR(3)

    SET @MonStr = CAST(@Money AS VARCHAR(100))

    SET @MonName = ''

    IF CAST(RIGHT(@MonStr,2) AS INT) > 0

    BEGIN

    SET @TempMon = RIGHT(@MonStr,2)

    SET @MonName = 'Cents'

    SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(@TempMon,1) WHEN 1 THEN @TempMon ELSE RIGHT(@TempMon,1) END)

    IF LEFT(@TempMon,1) NOT IN (0,1)

    SELECT @MonName = numname + (CASE LTRIM(@MonName) WHEN 'CENTS' THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(@TempMon,1) + '0') AS int)

    IF LEN(@MonStr) = 4 AND LEFT(@MonStr,1) = 0

    BEGIN

    SET @MonName = @MonName

    END

    ELSE

    SET @MonName = 'And ' + @MonName

    END

    SET @MonStr = LEFT(@MonStr,LEN(@MonStr) - 3)

    /* Ones, Tens Hundreds */

    IF LEN(@MonStr) > 0

    BEGIN

    SET @TempMon = RIGHT(@MonStr,3)

    SET @MonStr = LEFT(@MonStr, LEN(@MonStr) - LEN(@TempMon))

    If CAST(@TempMon AS INT) != 0

    BEGIN

    SET @MonName = 'Dollars ' + @MonName

    SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(RIGHT(@TempMon,2),1) WHEN 1 THEN RIGHT(@TempMon,2) ELSE RIGHT(@TempMon,1) END)

    IF LEN(@TempMon) > 1

    BEGIN

    IF LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)

    SELECT @MonName = numname + (CASE RIGHT(@TempMon,1) WHEN 0 THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(RIGHT(@TempMon,2),1) + '0') AS int)

    END

    IF CHARINDEX('Dollars',@MonName) > 1 AND (LEN(@TempMon) = 3 OR LEN(@MonStr) > 0)

    IF CHARINDEX(' And ',@MonName) = 0

    SET @MonName = 'And ' + @MonName

    IF LEN(@TempMon) = 3 AND CAST(LEFT(@TempMon,1) AS INT) != 0

    SELECT @MonName = numname + ' Hundred ' + @MonName FROM NumNameTbl WHERE val = CAST(LEFT(@TempMon,1) AS INT)

    END

    END

    /* Thousands */

    IF LEN(@MonStr) > 0

    BEGIN

    SET @TempMon = RIGHT(@MonStr,3)

    SET @MonStr = LEFT(@MonStr, LEN(@MonStr) - LEN(@TempMon))

    If CAST(@TempMon AS INT) != 0

    BEGIN

    IF CHARINDEX('Dollars',@MonName) = 0

    SET @MonName = 'Dollars ' + @MonName

    SET @MonName = 'Thousand ' + @MonName

    SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(RIGHT(@TempMon,2),1) WHEN 1 THEN RIGHT(@TempMon,2) ELSE RIGHT(@TempMon,1) END)

    IF LEN(@TempMon) > 1

    BEGIN

    IF LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)

    SELECT @MonName = numname + (CASE RIGHT(@TempMon,1) WHEN 0 THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(RIGHT(@TempMon,2),1) + '0') AS int)

    END

    IF LEN(@TempMon) = 3 AND CAST(LEFT(@TempMon,1) AS INT) != 0

    SELECT @MonName = numname + ' Hundred ' + @MonName FROM NumNameTbl WHERE val = CAST(LEFT(@TempMon,1) AS INT)

    END

    END

    /* Million */

    IF LEN(@MonStr) > 0

    BEGIN

    SET @TempMon = RIGHT(@MonStr,3)

    SET @MonStr = LEFT(@MonStr, LEN(@MonStr) - LEN(@TempMon))

    If CAST(@TempMon AS INT) != 0

    BEGIN

    IF CHARINDEX('Dollars',@MonName) = 0

    SET @MonName = 'Dollars ' + @MonName

    SET @MonName = 'Million ' + @MonName

    SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(RIGHT(@TempMon,2),1) WHEN 1 THEN RIGHT(@TempMon,2) ELSE RIGHT(@TempMon,1) END)

    IF LEN(@TempMon) > 1

    BEGIN

    IF LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)

    SELECT @MonName = numname + (CASE RIGHT(@TempMon,1) WHEN 0 THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(RIGHT(@TempMon,2),1) + '0') AS int)

    END

    IF LEN(@TempMon) = 3 AND CAST(LEFT(@TempMon,1) AS INT) != 0

    SELECT @MonName = numname + ' Hundred ' + @MonName FROM NumNameTbl WHERE val = CAST(LEFT(@TempMon,1) AS INT)

    END

    END

    /* Billion */

    IF LEN(@MonStr) > 0

    BEGIN

    SET @TempMon = RIGHT(@MonStr,3)

    SET @MonStr = LEFT(@MonStr, LEN(@MonStr) - LEN(@TempMon))

    If CAST(@TempMon AS INT) != 0

    BEGIN

    IF CHARINDEX('Dollars',@MonName) = 0

    SET @MonName = 'Dollars ' + @MonName

    SET @MonName = 'Billion ' + @MonName

    SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(RIGHT(@TempMon,2),1) WHEN 1 THEN RIGHT(@TempMon,2) ELSE RIGHT(@TempMon,1) END)

    IF LEN(@TempMon) > 1

    BEGIN

    IF LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)

    SELECT @MonName = numname + (CASE RIGHT(@TempMon,1) WHEN 0 THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(RIGHT(@TempMon,2),1) + '0') AS int)

    END

    IF LEN(@TempMon) = 3 AND CAST(LEFT(@TempMon,1) AS INT) != 0

    SELECT @MonName = numname + ' Hundred ' + @MonName FROM NumNameTbl WHERE val = CAST(LEFT(@TempMon,1) AS INT)

    END

    END

    IF CHARINDEX('One',@MonName) = 1 AND CHARINDEX('Dollars',@MonName) = 5

    SET @MonName = REPLACE(@MonName,'Dollars','Dollar')

    SELECT @MonName AS MoneyName

    GO

  • Thomas Rushton

    SSC-Insane

    Points: 22648

    Antares,

    Thanks for that. Sorry I'm a bit late, but it's been a bad week...

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • Antares686

    SSC Guru

    Points: 125444

    Hope it gives you what you need to get your results and heres to a better week from now on.

  • don1941

    SSCarpal Tunnel

    Points: 4183

    quote:


    We need to express an integer (eg 1034) as words ("one thousand and thirty four").

    Does anyone here know of anything that'll do this conversion for us, or are we going to have to figure it out for ourselves?


    trot on over to the script library or just click the "home" link here. i contributed a user-defined function for that a couple of days ago.

  • Thomas Rushton

    SSC-Insane

    Points: 22648

    quote:


    Hope it gives you what you need to get your results and heres to a better week from now on.


    I don't know about a better week...

    I had made a start on this problem just after I posted the request here. However, I had to leave it in a hurry and go on to other things (including hospitals...)

    Anyway, herewith my solution (which doesn't use a table, but does use two functions to do the job):

    -----code-----

    USE css

    GO

    CREATE FUNCTION dbo.fn_inttowords_subfunction (@val decimal(3,0))

    RETURNS varchar(255) AS

    BEGIN

    declare @Ret varchar(255)

    declare @Hundreds varchar(50)

    declare @SubHundreds varchar(50)

    declare @Units varchar(50)

    declare @Dig1 int

    declare @Dig2 int

    declare @Dig3 int

    declare @Digits varchar(3)

    declare @LowTwos int

    SET @Dig1 = 0

    SET @Dig2 = 0

    SET @Dig3 = 0

    SET @Digits = convert(varchar(3), @val)

    if (datalength(@Digits) = 1)

    BEGIN

    SET @Dig3 = convert(int, @Digits)

    END

    IF (datalength(@Digits) = 2)

    BEGIN

    SET @Dig3 = convert(int, SubString(@Digits,2,1))

    SET @Dig2 = convert(int, SubString(@Digits, 1, 1))

    END

    IF (DataLength(@Digits) = 3)

    BEGIN

    SET @Dig3 = convert(int, SubString(@Digits, 3, 1))

    SET @Dig2 = convert(int, Substring(@Digits, 2, 1))

    SET @Dig1 = convert(int, Substring(@Digits, 1, 1))

    END

    SET @LowTwos = convert(int, @Dig2 + @Dig3)

    SET @Hundreds =

    CASE @Dig1

    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

    IF (@Dig2 > 1)

    BEGIN

    SET @SubHundreds =

    CASE @Dig2

    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

    END

    IF (@Dig2 = 1)

    BEGIN

    SET @SubHundreds =

    CASE @Dig2 * 10 + @Dig3

    WHEN 19 THEN 'Nineteen'

    WHEN 18 THEN 'Eighteen'

    WHEN 17 THEN 'Seventeen'

    WHEN 16 THEN 'Sixteen'

    WHEN 15 THEN 'Fifteen'

    WHEN 14 THEN 'Fourteen'

    WHEN 13 THEN 'Thirteen'

    WHEN 12 THEN 'Twelve'

    WHEN 11 THEN 'Eleven'

    WHEN 10 THEN 'Ten'

    END

    END

    IF (@Dig2 <> 1)

    BEGIN

    SET @Units =

    CASE @Dig3

    WHEN 9 THEN 'Nine'

    WHEN 8 THEN 'Eight'

    WHEN 7 THEN 'Seven'

    WHEN 6 THEN 'Six'

    WHEN 5 THEN 'Five'

    WHEN 4 THEN 'Four'

    WHEN 3 THEN 'Three'

    WHEN 2 THEN 'Two'

    WHEN 1 THEN 'One'

    WHEN 0 THEN ''

    END

    IF ( (@SubHundreds <> '') AND NOT (@SubHundreds IS NULL) )

    BEGIN

    SET @SubHundreds = @SubHundreds + ' ' + @Units

    END

    ELSE

    BEGIN

    SET @SubHundreds = @Units

    END

    END

    IF ( (@Hundreds = '') OR (@Hundreds IS NULL) )

    BEGIN

    SET @Ret = @SubHundreds

    END

    ELSE

    BEGIN

    SET @Ret = @Hundreds

    IF ( (@SubHundreds <> '') AND NOT (@SubHundreds IS NULL) )

    BEGIN

    SET @Ret = @Ret + ' and ' + @SubHundreds

    END

    END

    return @Ret

    END

    GO

    CREATE FUNCTION dbo.fn_inttowords(@val int)

    RETURNS VARCHAR(255) AS

    BEGIN

    declare @Ret varchar(255)

    declare @Ones int

    declare @Thousands int

    declare @Millions int

    declare @Billions int

    declare @OnesString varchar(255)

    declare @ThousandsString varchar(255)

    declare @MillionsString varchar(255)

    declare @BillionsString varchar(255)

    set @Ones = @val % 1000

    set @Thousands = (@val/1000) % 1000

    set @Millions = (@val/1000000) % 1000

    set @Billions = (@val/1000000000) % 1000

    IF (@Billions > 0)

    BEGIN

    SET @BillionsString = dbo.fn_inttowords_subfunction(@Billions)

    END

    IF (@Millions > 0)

    BEGIN

    SET @MillionsString = dbo.fn_inttowords_subfunction(@Millions)

    END

    IF (@Thousands > 0)

    BEGIN

    SET @ThousandsString = dbo.fn_inttowords_subfunction(@Thousands)

    END

    IF (@Ones > 0)

    BEGIN

    SET @OnesString = dbo.fn_inttowords_subfunction(@Ones)

    END

    IF NOT (@BillionsString IS NULL)

    BEGIN

    SET @Ret = @BillionsString + ' Billion'

    END

    IF NOT (@MillionsString IS NULL)

    BEGIN

    IF NOT (@Ret IS NULL)

    BEGIN

    SET @Ret = @Ret + ' ' + @MillionsString + ' Million'

    END

    ELSE

    BEGIN

    SET @Ret = @MillionsString + ' Million'

    END

    END

    IF NOT (@ThousandsString IS NULL)

    BEGIN

    IF NOT (@Ret IS NULL)

    BEGIN

    SET @Ret = @Ret + ' ' + @ThousandsString + ' Thousand'

    END

    ELSE

    BEGIN

    SET @Ret = @ThousandsString + ' Thousand'

    END

    END

    IF NOT (@OnesString IS NULL)

    BEGIN

    IF NOT (@Ret IS NULL)

    BEGIN

    SET @Ret = @Ret + ' ' + @OnesString

    END

    ELSE

    BEGIN

    SET @Ret = @OnesString

    END

    END

    RETURN IsNull(@Ret, 'zero')

    END

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • Antares686

    SSC Guru

    Points: 125444

    Interesting approach without a table. If you test I suggest posting as an alternative to the scripts section.

  • Thomas Rushton

    SSC-Insane

    Points: 22648

    I have submitted it to the scripts section already.

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

Viewing 9 posts - 1 through 9 (of 9 total)

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