Converting Numeric value to Word with the Help of Sql Server 2000

**Joyjeet** · 20-11-2009

Hi, Can anyone solve my query. I am trying to make a query which will take my salary value and then simply gives it output in Word format. Means If I am providing input of 100, then it has to give me reply as Hundred. Can it be possible? If yes, then please reply me.

**Praetor** · 20-11-2009

Hi, I don't seem any query which will provide you this. I have "SQL Server 2000-Complete Reference", but not find anything which will convert numeric value to the word format. I think there will be any inbuilt function which will provide you this facility. I had searched on internet also, but not find anything regarding this. If you getting answer of this then forward me also. I have tried one, if you able to complete just make it.

Code:

Select <one of the function>(Salary) from <Tablename>
where id==<id>

**Modifier** · 20-11-2009

Hi, you have posted a tricky question which really confused me. I had tried but not able to convert numeric value to word. One of my friend who is working on SQL gave me some solution, but that also I am not able to get. If you are getting anything then your luck. Just go through it.

Code:

CREATE FUNCTION [dbo].[curto_word](@Input varchar(3),@Group tinyint,@Currency tinyint)
RETURNS varchar(8000)
AS
BEGIN

SET @Input = right('000' + @Input,3)

DECLARE @Groups TABLE
([Group] tinyint
,Currency tinyint
,Word varchar(10))

INSERT INTO @Groups
SELECT 5,NULL,'Billion '
UNION
SELECT 4,NULL,'Million '
UNION
SELECT 3,NULL,'Thousand '
UNION
SELECT 2,1,'Pound# '
UNION
SELECT 2,2,'Euro# '
UNION
SELECT 2,3,'Dollar# '
UNION
SELECT 1,1,'Pence '
UNION
SELECT 1,2,'Cent~ '
UNION
SELECT 1,3,'Cent~ '

DECLARE @Numbers TABLE
(Number char(2)
,Word varchar(10))

INSERT INTO @Numbers
SELECT '01','One ' UNION SELECT '02','Two ' UNION SELECT '03','Three ' UNION SELECT '04','Four ' UNION SELECT '05','Five ' UNION SELECT '06','Six ' UNION SELECT '07','Seven ' UNION SELECT '08','Eight ' UNION SELECT '09','Nine ' UNION SELECT '10','Ten ' UNION
SELECT '11','Eleven ' UNION SELECT '12','Twelve ' UNION SELECT '13','Thirteen ' UNION SELECT '14','Fourteen ' UNION SELECT '15','Fifteen ' UNION SELECT '16','Sixteen ' UNION SELECT '17','Seventeen ' UNION SELECT '18','Eighteen ' UNION SELECT '19','Nineteen ' UNION SELECT '20','Twenty ' UNION
SELECT '30','Thirty ' UNION SELECT '40','Forty ' UNION SELECT '50','Fifty ' UNION SELECT '60','Sixty ' UNION SELECT '70','Seventy ' UNION SELECT '80','Eighty ' UNION SELECT '90','Ninety '

DECLARE @Output varchar(100)

SET @Output =
CASE --Hundreds
WHEN left(@Input,1) <> 0 THEN (SELECT Word FROM @Numbers WHERE Number = '0' + left(@Input,1)) + 'Hundred '
ELSE ''
END +
CASE --And
WHEN @Group <> 1 AND right(@Input,2) <> 0 AND @Input > 100 THEN 'And '
ELSE ''
END +
CASE --Units
WHEN @Input = 0 AND @Group = 1 THEN 'Zero '
WHEN right(@Input,2) BETWEEN 1 AND 20 THEN (SELECT Word FROM @Numbers WHERE Number = right(@Input,2))
WHEN right(@Input,2) > 20 THEN (SELECT Word FROM @Numbers WHERE Number = left(right(@Input,2),1) + '0') + ISNULL((SELECT Word FROM @Numbers WHERE Number = '0' + right(@Input,1)),'')
ELSE ''
END +
CASE
WHEN @Group = 2 OR (@Group <> 2 AND @Input <> 0) THEN (SELECT Word FROM @Groups WHERE [Group] = @Group AND ISNULL(Currency,@Currency) = @Currency)
ELSE ''
END

RETURN @Output

END

CREATE FUNCTION [dbo].[udf_CurrencyToWords] (@Input numeric(15,2),@Currency tinyint)
RETURNS varchar (8000)
AS
BEGIN

DECLARE @CharInput char(15)
--Pad the input
SET @CharInput = RIGHT('0000000000000' + convert(varchar(15),@Input),15)

DECLARE @Counter tinyint
SET @Counter = 1

DECLARE @InputSub varchar(3)
DECLARE @Group tinyint
DECLARE @Output varchar (8000)

--Get words for each group with some logic for concatenation
WHILE @Counter <= len(@CharInput)-2
BEGIN
SET @InputSub = replace(substring(@CharInput,@Counter,3),'.','')
SET @Group = (len(@CharInput)-@Counter+1)/3

SET @Output =
ISNULL(@Output,'')
+ CASE
WHEN (@Group = 1 AND @InputSub <> 0 AND @Input >= 1.00) OR (@Group = 2 AND @InputSub BETWEEN 1 AND 99 AND @Input > 1000) THEN 'And '
ELSE ''
END
+ CASE
WHEN (@Group = 1 AND @InputSub = 0 AND @Input >= 1.00) OR (@Group = 2 AND @InputSub = 0 AND @Input < 1.00) THEN ''
ELSE [dbo].[curto_word](@InputSub,@Group,@Currency)
END

SET @Counter = @Counter + 3
CONTINUE
END

--Fix plurals and return a plain Zero if required
SET @Output =
CASE
WHEN right(@CharInput,2) = '01' THEN replace(@Output,'~','')
ELSE replace(@Output,'~','s')
END
SET @Output =
CASE
WHEN @Input < 2 THEN replace(@Output,'#','')
ELSE replace(@Output,'#','s')
END

RETURN ltrim(rtrim(@Output))
END

**opaper** · 20-11-2009

Hi, you can try below code to convert numeric value to word with the help of sql server 2000 if you want to convert value starting from 1 to 999999. Just check how it works.

Code:

SELECT
CASE (n / 100000) % 10
    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'
    ELSE ''
  END
  +CASE WHEN (n / 100000) % 10 > 0 THEN ' Hundred' ELSE '' END
  +CASE WHEN (n / 1000) % 100 > 0 AND (n / 1000) > 100 THEN ' and ' ELSE '' END
  +CASE (n / 1000) % 100
    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'
    ELSE 
      CASE (n / 10000) % 10
        WHEN 2 THEN 'Twen'
        WHEN 3 THEN 'Thir'
        WHEN 4 THEN 'For'
        WHEN 5 THEN 'Fif'
        WHEN 6 THEN 'Six'
        WHEN 7 THEN 'Seven'
        WHEN 8 THEN 'Eight'
        WHEN 9 THEN 'Nine'
        ELSE ''
      END
      +CASE WHEN (n / 10000) % 10 > 0 THEN 'ty' ELSE '' END
      +CASE WHEN (n / 1000) % 10 > 0 AND (n / 10000) % 10 > 0 THEN ' ' ELSE '' END
      +CASE (n / 1000) % 10
        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'
        ELSE ''
      END
  END 
  +CASE WHEN (n / 1000) % 1000 > 0 THEN ' Thousand' ELSE '' END
  +CASE WHEN (n / 100) % 10 > 0 THEN ' ' ELSE '' END
  +CASE (n / 100) % 10
    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'
    ELSE ''
  END
  +CASE WHEN (n / 100) % 10 > 0 THEN ' hundred' ELSE '' END
  +CASE WHEN n % 100 > 0 AND n > 100 THEN ' and ' ELSE '' END
  +CASE n % 100
    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'
    ELSE 
      CASE (n / 10) % 10
        WHEN 2 THEN 'Twen'
        WHEN 3 THEN 'Thir'
        WHEN 4 THEN 'For'
        WHEN 5 THEN 'Fif'
        WHEN 6 THEN 'Six'
        WHEN 7 THEN 'Seven'
        WHEN 8 THEN 'Eight'
        WHEN 9 THEN 'Nine'
        ELSE ''
      END
      +CASE WHEN (n / 10) % 10 > 0 THEN 'ty' ELSE '' END
      +CASE WHEN n % 10 > 0 AND (n / 10) % 10 > 0 THEN ' ' ELSE '' END
      +CASE n % 10
        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'
        ELSE ''
      END
  END words
FROM YourTable