Function to express numeric value in words

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