Convert Number to Word in Sql Server

Prerequisite – SQL introduction
In SQL code groups of commands are arranged within a block. A block group related declarations or statements. In declare part, we declare variables and between begin and end part, we perform the operations.

Given a number and the task is to convert each digit of the number into words.

Approach is to select the corresponding word of a particular number using FnNumtoWords function.

Below is the required implementation:

Function 1

IF OBJECT_ID('FnConvertDigit') IS NOT NULL
DROP FUNCTION FnConvertDigit
GO
CREATE Function [dbo].[FnConvertDigit](@decNumber decimal)
RETURNS VARCHAR(6)
AS
BEGIN
DECLARE
@strWords VARCHAR(6)
SELECT @strWords = CASE @decNumber
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
RETURN @strWords
END

Function 2 :

IF OBJECT_ID('FnConvertDigit') IS NOT NULL

DROP FUNCTION FnConvertDigit

GO

CREATE FUNCTION [dbo].[FnConvertTens](@decNumber varchar(2))

RETURNS VARCHAR(30)

AS

BEGIN

declare @strWords varchar(30)

--Is value between 10 and 19?

If Left(@decNumber, 1) = 1

BEGIN

SELECT @strWords = Case @decNumber

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

END

ELSE -- otherwise it's between 20 and 99.

BEGIN

SELECT @strWords = Case Left(@decNumber, 1)

WHEN '0' THEN ''

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

SELECT @strWords = @strWords + dbo.FnConvertDigit(Right(@decNumber, 1))

END

--Convert ones place digit.

RETURN @strWords

END 

GO

Function 3

IF OBJECT_ID('FnNumToWords') IS NOT NULL

DROP FUNCTION FnNumToWords

GO

/*===================================================================================  

CREATED BY: SA  

Description: Converts Amount in Figures to Words

DATE : 07/17/2020 

USAGE :SELECT dbo.FnNumToWords(999999.99,'1')

=====================================================================================*/  

ALTER FUNCTION [dbo].[FnNumToWords] (@decNumber DECIMAL(12, 2),@Currency CHAR(1))

RETURNS VARCHAR(300)

AS

BEGIN

DECLARE

@strNumber VARCHAR(100),

@strCedis VARCHAR(200),

@strPesewas VARCHAR(100),

@strWords VARCHAR(300),

@Index INT,

@Flag INT,

@VCurrency CHAR(1)

IF ISNULL(@Currency,'') = ''

BEGIN

SET @VCurrency = '1'

END

ELSE

BEGIN

SET @VCurrency = @Currency

END

SELECT @strNumber = CAST(@decNumber AS VARCHAR(100))

SELECT @Index = CHARINDEX('.', @strNumber)

IF(@decNumber>999999.99 OR ISNULL(@decNumber,0) = 0) 

BEGIN 

RETURN ''

END 

IF @Index > 0

BEGIN 

SELECT @strPesewas = dbo.FnConvertTens(Right(@strNumber, LEN(@strNumber) - @Index))

SELECT @strNumber = SUBSTRING(@strNumber, 1, LEN(@strNumber) - LEN(SUBSTRING(@strNumber,CHARINDEX('.', @strNumber),LEN(@strNumber))))

IF LEN(@strPesewas) > 0 

SELECT @strPesewas = @strPesewas + CASE @VCurrency WHEN  '1' THEN ' pesewa(s)'

  WHEN  '2' THEN ' Cent(s)'

  WHEN  '3' THEN ' pence'

  WHEN  '4' THEN ' Cent(s)'

  END

END 

SELECT @strCedis = ''

SELECT @Index=LEN(@strNumber)

SELECT @Flag=2

WHILE (@Index > 0) 

BEGIN

IF (@Index=6)

BEGIN

IF(SUBSTRING(@strNumber,2,1)<>'0' OR SUBSTRING(@strNumber,3,1)<>'0' AND SUBSTRING(@strNumber,4,1)='0' AND SUBSTRING(@strNumber,5,1)='0' AND

SUBSTRING(@strNumber,6,1)='0' AND @Flag=2 and @strPesewas='')

BEGIN

IF LEN(@strCedis) <= 0

BEGIN

IF CONVERT(INT,SUBSTRING(@strNumber,1,1)) = 1

BEGIN

SELECT @strCedis=@strCedis+'' + dbo.fConvertDigit(SUBSTRING(@strNumber,1,1))+' Hundred '

SELECT @Flag=2

END

ELSE

BEGIN

SELECT @strCedis=@strCedis+'' + dbo.fConvertDigit(SUBSTRING(@strNumber,1,1))+' Hundred '

SELECT @Flag=2

END

END

ELSE

BEGIN

IF CONVERT(INT,SUBSTRING(@strNumber,1,1)) = 1

BEGIN

SELECT @strCedis=@strCedis+' and' + dbo.fConvertDigit(SUBSTRING(@strNumber,1,1))+' Hundred '

SELECT @Flag=1

END

ELSE

BEGIN

SELECT @strCedis=@strCedis+' and' + dbo.fConvertDigit(SUBSTRING(@strNumber,1,1))+' Hundred '

SELECT @Flag=1

END

END

END

ELSE

BEGIN

IF CONVERT(INT,SUBSTRING(@strNumber,1,1)) = 1

BEGIN

SELECT @strCedis=@strCedis+dbo.fConvertDigit(SUBSTRING(@strNumber,1,1))+' Hundred '

END

ELSE

BEGIN

SELECT @strCedis=@strCedis+dbo.fConvertDigit(SUBSTRING(@strNumber,1,1))+' Hundred '

END

END

SELECT @strNumber=SUBSTRING(@strNumber,2,LEN(@strNumber))

SELECT @Index=@Index-1

END

ELSE IF (@Index=5)

BEGIN

IF(SUBSTRING(@strNumber,1,1)='0')

BEGIN

IF SUBSTRING(@strNumber,2,1)<>'0'

BEGIN

IF(SUBSTRING(@strNumber,3,1)='0' AND SUBSTRING(@strNumber,4,1)='0' AND SUBSTRING(@strNumber,5,1)='0' AND @Flag=2 AND @strPesewas='')

BEGIN

SELECT @strCedis=@strCedis+' and ' +dbo.FnConvertDigit(SUBSTRING(@strNumber,2,1))+' Thousand '

SELECT @Flag=1

END

ELSE

BEGIN

SELECT @strCedis=@strCedis+dbo.FnConvertDigit(SUBSTRING(@strNumber,2,1))+' Thousand '

END

SELECT @strNumber=SUBSTRING(@strNumber,3,LEN(@strNumber))

SELECT @Index=@Index-2

END

ELSE IF SUBSTRING(@strNumber,2,1)='0'

BEGIN

SELECT @strCedis=@strCedis+dbo.FnConvertDigit(SUBSTRING(@strNumber,2,1))+' Thousand '

SELECT @strNumber=SUBSTRING(@strNumber,3,LEN(@strNumber))

SELECT @Index=@Index-2

END

     ELSE

     BEGIN

SELECT @strNumber=SUBSTRING(@strNumber,3,LEN(@strNumber))

SELECT @Index=@Index-2

     END

END

ELSE

BEGIN

IF(SUBSTRING(@strNumber,3,1)='0' AND SUBSTRING(@strNumber,4,1)='0' AND SUBSTRING(@strNumber,5,1)='0' AND @Flag=2 AND @strPesewas='')

BEGIN

SELECT @strCedis=@strCedis+dbo.FnConvertTens(SUBSTRING(@strNumber,1,2))+' Thousand '

SELECT @Flag=1

END

ELSE

BEGIN

SELECT @strCedis=@strCedis+dbo.FnConvertTens(SUBSTRING(@strNumber,1,2))+' Thousand '

END

SELECT @strNumber=SUBSTRING(@strNumber,3,LEN(@strNumber))

SELECT @Index=@Index-2

END

END

ELSE IF (@Index=4)

BEGIN

IF ( (SUBSTRING(@strNumber,3,1)<>'0' AND SUBSTRING(@strNumber,4,1)<>'0') AND SUBSTRING(@strNumber,2,1)='0' AND @Flag=2 AND @strPesewas='')

BEGIN

SELECT @strCedis=@strCedis+' and' + dbo.FnConvertDigit(SUBSTRING(@strNumber,1,1))+' Thousand '

SELECT @Flag=1

END

ELSE

BEGIN

SELECT @strCedis=@strCedis+dbo.FnConvertDigit(SUBSTRING(@strNumber,1,1))+' Thousand '

END

SELECT @strNumber=SUBSTRING(@strNumber,2,LEN(@strNumber))

SELECT @Index=@Index-1

END

ELSE IF (@Index=3)

BEGIN

IF SUBSTRING(@strNumber,1,1)<>'0'

BEGIN

SELECT @strCedis=@strCedis+dbo.FnConvertDigit(SUBSTRING(@strNumber,1,1))+' Hundred '

SELECT @strNumber=SUBSTRING(@strNumber,2,LEN(@strNumber))

IF( (SUBSTRING(@strNumber,1,1)<>'0' OR SUBSTRING(@strNumber,2,1)<>'0') AND @Flag=2 )

BEGIN

SELECT @strCedis=@strCedis+' and '

SELECT @Flag=1

END

SELECT @Index=@Index-1

END

ELSE

BEGIN

SELECT @strNumber=SUBSTRING(@strNumber,2,LEN(@strNumber))

SELECT @Index=@Index-1

END

END

    ELSE IF (@Index=2)

BEGIN

IF SUBSTRING(@strNumber,1,1)<>'0'

BEGIN

SELECT @strCedis=@strCedis+dbo.FnConvertTens(SUBSTRING(@strNumber,1,2))

SELECT @Index=@Index-2

END

ELSE

BEGIN

SELECT @Index=@Index-1

END

END

ELSE IF (@Index=1)

BEGIN

IF (@strNumber<>'0')

BEGIN

SELECT @strCedis=@strCedis+dbo.FnConvertDigit(@strNumber)

END

SELECT @Index=@Index-1

END

CONTINUE

END

IF LEN(@strCedis)>0 SELECT @strCedis=@strCedis+ CASE @VCurrency    WHEN  '1' THEN ' Cedi(s) '

  WHEN  '2' THEN ' Dollar(s) '

  WHEN  '3' THEN ' Pound(s) '

  WHEN  '4' THEN ' Euro(s) '

  END

IF(LEN(@strPesewas)<>0)

BEGIN

IF LEN(@strCedis)>0 SELECT @strCedis=@strCedis + ' and '

END

SELECT @strWords = ISNULL(@strCedis, '') + ISNULL(@strPesewas, '')

SELECT @strWords = @strWords + ' only'

RETURN @strWords

END 

GO