Technical Article

Convert Currency to English

,

Just plug this into your Query Analyser and play. This code is an easier way of converting currency to English which can be altered easily to suit your needs.

I found that this script will help in avoiding the storage of Amount in Words everytime an amount is stored in applicatons that require the functionality.

CREATE FUNCTION dbo.ConvertDigit
(
@MyDigit VARCHAR(300)
)
RETURNS VARCHAR(300)
AS 

BEGIN

DECLARE @DigitInWords VARCHAR(300)

SET @DigitInWords=

CASE 

WHEN @MyDigit='1' THEN 'One'

WHEN @MyDigit='2' THEN 'Two'

WHEN @MyDigit='3' THEN 'Three'

WHEN @MyDigit='4' THEN 'Four'

WHEN @MyDigit='5' THEN 'Five'

WHEN @MyDigit='6' THEN 'Six'

WHEN @MyDigit='7' THEN 'Seven'

WHEN @MyDigit='8' THEN 'Eight'

WHEN @MyDigit='9' THEN 'Nine'

ELSE ''

END

RETURN @DigitInWords

END

 

GO

CREATE FUNCTION dbo.ConvertTens
(
@MyTens VARCHAR(300)
)

RETURNS VARCHAR(300)

AS

BEGIN 

DECLARE @Result VARCHAR(300)

-- Is value between 10 and 19?

IF (LEFT(@MyTens, 1)) = '1'

BEGIN 

SET @Result=

CASE 

WHEN @MyTens ='10' THEN 'Ten'

WHEN @MyTens ='11' THEN 'Eleven'

WHEN @MyTens ='12' THEN 'Twelve'

WHEN @MyTens ='13' THEN 'Thirteen'

WHEN @MyTens ='14' THEN 'Fourteen'

WHEN @MyTens ='15' THEN 'Fifteen'

WHEN @MyTens ='16' THEN 'Sixteen'

WHEN @MyTens ='17' THEN 'Seventeen'

WHEN @MyTens ='18' THEN 'Eighteen'

WHEN @MyTens ='19' THEN 'Nineteen'

ELSE ''

END

END

ELSE

BEGIN

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

SET @Result=

CASE 

WHEN (LEFT(@MyTens, 1))='2' THEN 'Twenty '

WHEN (LEFT(@MyTens, 1))='3' THEN 'Thirty '

WHEN (LEFT(@MyTens, 1))='4' THEN 'Forty '

WHEN (LEFT(@MyTens, 1))='5' THEN 'Fifty '

WHEN (LEFT(@MyTens, 1))='6' THEN 'Sixty '

WHEN (LEFT(@MyTens, 1))='7' THEN 'Seventy '

WHEN (LEFT(@MyTens, 1))='8' THEN 'Eighty '

WHEN (LEFT(@MyTens, 1))='9' THEN 'Ninety '

ELSE ''

END

-- Convert ones place digit.

SET @Result =ISNULL(@Result,'') + dbo.ConvertDigit(RIGHT(@MyTens, 1))

END

RETURN @Result

END

 

GO

CREATE FUNCTION dbo.ConvertHundreds
(

@MyNumber VARCHAR(300)

)

RETURNS VARCHAR(300)

AS

BEGIN

DECLARE @Result VARCHAR(300)

-- Exit if there is nothing to convert.

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

BEGIN 

RETURN ''

END


-- Append leading zeros to number.

SET @MyNumber = RIGHT('000' + @MyNumber, 3)


-- Do we have a hundreds place digit to convert?

IF LEFT(@MyNumber, 1) <> '0'

BEGIN

SET @Result = dbo.ConvertDigit(LEFT(@MyNumber, 1)) + ' Hundred '

END

-- Do we have a tens place digit to convert?

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

BEGIN

SET @Result = ISNULL(@Result,'') + dbo.ConvertTens( SUBSTRING( @MyNumber, 2,LEN(@MyNumber) ) )

END 

ELSE

BEGIN

-- If not, convert the ones place digit.

SET @Result = ISNULL(@Result,'') + dbo.ConvertDigit(SUBSTRING(@MyNumber, 3,LEN(@MyNumber)))

END

RETURN @Result

END

 

GO

CREATE FUNCTION ConvertCurrencyToEnglish

(

@MyNumber VARCHAR(200)

)

RETURNS VARCHAR(300)

AS

BEGIN

DECLARE @Temp VARCHAR(300)

DECLARE @Dollars VARCHAR(300), @Cents VARCHAR(300)

DECLARE @DecimalPlace INT, @Count INT

DECLARE @MultiplicationFactor VARCHAR(50)


-- Convert MyNumber to a string, trimming extra spaces.

SET @MyNumber = CAST(@MyNumber AS DECIMAL(18,2))


-- Find decimal place.

SET @DecimalPlace = CHARINDEX( '.' , @MyNumber)

-- If we find decimal place...

IF @DecimalPlace > 0

BEGIN

-- Convert cents

SET @Temp = LEFT(SUBSTRING(@MyNumber, @DecimalPlace + 1,LEN(@MyNumber)) + '00', 2)

SET @Cents = dbo.ConvertTens(@Temp)

-- Strip off cents from remainder to convert.

SET @MyNumber = RTRIM(LEFT(@MyNumber, @DecimalPlace - 1))

END


SET @Count = 1


WHILE ISNULL(@MyNumber,'') <> ''

BEGIN

-- Convert last 3 digits of MyNumber to English dollars.

SET @Temp = dbo.ConvertHundreds(RIGHT(@MyNumber, 3))


IF ISNULL(@Temp,'') <> ''

BEGIN 

SET @MultiplicationFactor =

CASE 

WHEN @Count=2 THEN ' Thousand '

WHEN @Count=3 THEN ' Million '

WHEN @Count=4 THEN ' Billion '

WHEN @Count=5 THEN ' Trillion '

ELSE ''

END


SET @Dollars = ISNULL(@Temp,'') + @MultiplicationFactor + ISNULL(@Dollars,'')

END 


IF LEN(@MyNumber) > 3

BEGIN

-- Remove last 3 converted digits from MyNumber.

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

END

ELSE

BEGIN

SET @MyNumber = ''

END

SET @Count = @Count + 1

END

-- Clean up dollars.

SET @Dollars=

CASE 

WHEN ISNULL(@Dollars,'') = '' THEN 'No Dollars'

WHEN ISNULL(@Dollars,'') = 'One' THEN 'One Dollar'

ELSE ISNULL(@Dollars,'') + ' Dollars'

END


-- Clean up cents.

SET @Cents= 

CASE 

WHEN @Cents = '' THEN ' And No Cents'

WHEN @Cents = 'One' THEN ' And One Cent'

ELSE ' And ' + ISNULL(@Cents,'') + ' Cents'

END


--@MultiplicationFactor 

RETURN ISNULL(@Dollars,'') + ISNULL(@Cents,'')

 

END

GO

SELECT dbo.ConvertCurrencyToEnglish('2115600000.14'),'2115600000.14'

SELECT dbo.ConvertHundreds('787'),'787'

SELECT dbo.ConvertTens('87'),'87'

SELECT dbo.ConvertDigit('9')

Rate

Share

Share

Rate