Convert Money to Indian Currency Format


This is an User Defined Function to convert the Money to Indian Currency Format.

I searched in google an found nothing on converting money to indian currency format, there were some codes to convert money to U.S currency format. So i decided to write one for my own.

here is the function


CREATE FUNCTION dbo.udf_MoneyFormat
(
@amount MONEY
)
RETURNS VARCHAR(50)
AS
/* --Comments--
Created By: Alwyn Duraisingh.M
Created On: 2nd March 2010
Purpose: To convert a Money to Indian Currency Format
*/
BEGIN
DECLARE @charMoney VARCHAR(50), @RemainingChar VARCHAR(50)
,QuotChar VARCHAR(50)
DECLARE @LenStr INT, @val INT, @index INT
SELECT @charMoney = CONVERT(VARCHAR(50),@amount)
SELECT @QuotChar = SUBSTRING(@charMoney,1,CHARINDEX('.',@charMoney)-1)
SELECT @val = LEN(@charMoney) - LEN(@QuotChar)
SELECT @RemainingChar = SUBSTRING(@charMoney,CHARINDEX('.',@charMoney),@val)
SELECT @LenStr = LEN(@QuotChar)
SET @index = 3
WHILE (@LenStr > @index)
BEGIN
SET @QuotChar = (SELECT STUFF(@QuotChar, (@LenStr-@index) + 1, 0, ','))
SET @index = @index + 2
END
RETURN @QuotChar + @RemainingChar
END


You can call this function like;


DECLARE @amount Money
SET @amount = 1000023.08
SELECT dbo.udf_MoneyFormat(@amount)

RESULT:
-------
10,00,023.08


Hope this would help you all, as i have shared it through DNS


Comments

Author: saravanakumar26 May 2010 Member Level: Silver   Points : 2

Hi


// Create a Windows Application
// Create 1 text box and one Button
// Place the below code correct the line spaces :)


public string BuildString="";

private void button1_Click(object sender, System.EventArgs e)
{
string strText = textBox1.Text.Trim();
Recursion(strText); //Pass Only whole number (Integers) if you have decimal use substring and add it later
MessageBox.Show("Rs." + BuildString.Replace(",,",","));
BuildString = "";
}

private string Recursion(string strBuild)
{
string MainString = strBuild;
string Tempstring = "";


if (!(MainString.Length <= 3 && BuildString.Length == 0)) // if it is less than 3 digits no need to format.
{
if(BuildString.Length == 0) // if its > 0 alreay this function called, so we need to take care of 2 digits
{
Tempstring = MainString.Substring(0,MainString.Length-3);
if (Tempstring.Length > 2 )
{
BuildString = "," + MainString.Substring(MainString.Length-3,3);
Recursion(Tempstring);
}
else
{
if(BuildString == "")
BuildString = Tempstring + "," + MainString.Substring(MainString.Length-3,3);
else
BuildString = Tempstring + "," + BuildString;

}
}
else
{
Tempstring = MainString.Substring(0,MainString.Length-2);
if (Tempstring.Length > 2 )
{
BuildString = "," + MainString.Substring(MainString.Length-2,2) + "," + BuildString;
Recursion(Tempstring);
}
else
{
if(Tempstring.Length == 1)
BuildString = MainString.Substring(0,MainString.Length-2) +"," + MainString.Substring(1) + BuildString;
else
BuildString = Tempstring + "," + MainString.Substring(2,MainString.Length-2) + BuildString;
}
}
}
else
{
BuildString = strBuild;
}
return BuildString;
}


for more info

http://saravanakumaar.blogspot.com/2010/05/number-into-indian-rs-format-currency.html

Author: Alwyn Duraisingh26 May 2010 Member Level: Gold   Points : 1

The above is not a sql Server Code, Saravana kumar could have posted it in the resource section.

Guest Author: sushil13 Dec 2012

VERY USEFUL FUNCTION

TO HANDLE NEGATIVE VALUES, USE IT LIKE THIS...

DECLARE @amount Money
SET @amount = -125544823.95
SELECT CASE WHEN ISNULL(@amount,0) > 0 THEN dbo.fnMoneyFormat(@amount)
ELSE '-' +dbo.fnMoneyFormat(-(@amount)) END

Guest Author: sushil13 Dec 2012

VERY USEFUL FUNCTION

TO HANDLE NEGATIVE VALUES, USE IT LIKE THIS...

DECLARE @amount Money
SET @amount = -125544823.95
SELECT CASE WHEN ISNULL(@amount,0) > 0 THEN dbo.fnMoneyFormat(@amount)
ELSE '-' +dbo.fnMoneyFormat(-(@amount)) END

Guest Author: sunil sahu10 Jan 2014

Dim cost As Double = 2524000
Dim str As String = cost.ToString("C", New System.Globalization.CultureInfo("hi-IN"))
str = str.Remove(0, 3)
Label1.Text = str



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: