Conversion of Numbers to Words

• arunraj_c

  SSC Enthusiast

  Points: 119

  More actions

  April 8, 2002 at 9:43 pm

  #78598

  Conversion of Numbers to Words

  I want to convert the numbers to words using Stored Procedure in SQL Server 7.0 or SQL Server 2000, as follows $1525.25 to "One Thousand Five Hundred and Twenty Five Dollars and Twenty Five Cents".

  If any one knows please send me the procedure

  Arun Raj.C

  
  Arun Raj.C

• Antares686

  SSC Guru

  Points: 125444

  More actions

  April 9, 2002 at 7:18 am

  #426861

  Try this, works great for me (note: only goes to billions at this point, but easy enough to expand). May work even better as a function in 2000.

  /* 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)

  SELECT @MonName = numname + ' Cents' 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 + '-' + @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

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

  END

  /* 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 LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)

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

  IF CHARINDEX('Dollars',@MonName) > 1 AND (LEN(@TempMon) = 3 OR LEN(@MonStr) > 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 @TempMon ELSE RIGHT(@TempMon,1) END)

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

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

  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 @TempMon ELSE RIGHT(@TempMon,1) END)

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

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

  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 @TempMon ELSE RIGHT(@TempMon,1) END)

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

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

  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

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)