January 20, 2013 at 10:51 pm
Hi,
I Created Function For Indian Money Format
CREATE function [dbo].[Fn_Indian_Money_Format]
(
@amount MONEY
)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @charMoney VARCHAR(50), @RemainingChar VARCHAR(50), @QuoteChar VARCHAR(50)
DECLARE @LenStr INT, @val INT, @index INT
SELECT @charMoney = CONVERT(VARCHAR(50),@amount)
SELECT @QuoteChar = SUBSTRING(@charMoney,1,CHARINDEX('.',@charMoney)-1)
SELECT @val = LEN(@charMoney) - LEN(@QuoteChar)
SELECT @RemainingChar = SUBSTRING(@charMoney,CHARINDEX('.',@charMoney),@val)
SELECT @LenStr = LEN(@QuoteChar)
SET @index = 3
WHILE (@LenStr > @index)
BEGIN
SET @QuoteChar = (SELECT STUFF(@QuoteChar, (@LenStr-@index) + 1, 0, ','))
END
RETURN isnull(left(@QuoteChar + @RemainingChar, len(@QuoteChar) + len(@RemainingChar)-3),0)
END
1.Requirement
Its Working Fine in Positive Amount
For Example:
select dbo.fn_Format_Money_Indian (1000) -- Result is 1,000
select dbo.fn_Format_Money_Indian (10000) -- Result is 10,000
select dbo.fn_Format_Money_Indian (100000) -- Result is 1,00,000
select dbo.fn_Format_Money_Indian (1000000) -- Result is 10,00,000
But Its not Working Fine in Negative Amount
For Example:
select dbo.fn_Format_Money_Indian (-10000) -- Result is -,10,000 But Reqired Format is -10,000
select dbo.fn_Format_Money_Indian (-1000000) -- Result is -,10,00,000 But Reqired Format is -10,00,000
select dbo.fn_Format_Money_Indian (-100000000) -- Result is -,10,00,00,000 But Reqired Format is -10,00,00,000
How To Solve The Issue...
2.Requirement
Is it Possible to Return Type as Float or Numeric Type...
Please Give Me The Solution As Soon as Possible
Thanks & Regards,
D.Saravanan
January 21, 2013 at 12:27 am
you wrote function return time float or numeric type .but when you add "," in numeric to separate value numeric value no longer will be numeric.if you want format that you specified then function has to be return datatype that supports string.
January 21, 2013 at 12:58 am
I would personally format the data in the front end tool as you are more likley to have better functions (pre 2012) to handle this, such as the vb/c#/RS, FORMAT function.
As a QUICK fix you change the code to do an ABS on the covert from money to String. Then Use a CASE SIGN(@Amount) WHEN -1 THEN '-' END to add in the negative on the return (see below)
(I also made a change to use SET rather than SELECT out of personal preference)
CREATE function [dbo].[Fn_Indian_Money_Format]
(
@amount MONEY
)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @charMoney VARCHAR(50), @RemainingChar VARCHAR(50), @QuoteChar VARCHAR(50)
DECLARE @LenStr INT, @val INT, @index INT
Set @charMoney = CONVERT(VARCHAR(50),ABS(@amount))
Set @QuoteChar = SUBSTRING(@charMoney,1,CHARINDEX('.',@charMoney)-1)
Set @val = LEN(@charMoney) - LEN(@QuoteChar)
Set @RemainingChar = SUBSTRING(@charMoney,CHARINDEX('.',@charMoney),@val)
Set @LenStr = LEN(@QuoteChar)
SET @index = 3
WHILE (@LenStr > @index)
BEGIN
SET @QuoteChar = (SELECT STUFF(@QuoteChar, (@LenStr-@index) + 1, 0, ','))
END
RETURN CASE SIGN(@amount) When -1 then '-' END+ isnull(left(@QuoteChar + @RemainingChar, len(@QuoteChar) + len(@RemainingChar)-3),0)
END
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 21, 2013 at 5:13 am
Have you considered using a more efficient inline table-valued function instead? Here are a couple of ideas:
DECLARE @amount MONEY
SET @amount = -3211987654321.10 -- [-3,210,987,654,321.10]
---------------------------------------------------------------------------------------
SELECT
FormattedAmount = CASE SIGN(@amount) WHEN -1 THEN '-' ELSE '' END +
CASE
WHEN vl < 4 THEN AmountAsString
WHEN vl < 6 THEN STUFF(AmountAsString,vl-2,0,',')
WHEN vl < 8 THEN STUFF(STUFF(AmountAsString,vl-2,0,','),vl-4,0,',')
WHEN vl < 10 THEN STUFF(STUFF(STUFF(AmountAsString,vl-2,0,','),vl-4,0,','),vl-6,0,',')
WHEN vl < 12 THEN STUFF(STUFF(STUFF(STUFF(AmountAsString,vl-2,0,','),vl-4,0,','),vl-6,0,','),vl-8,0,',')
WHEN vl < 14 THEN STUFF(STUFF(STUFF(STUFF(STUFF(AmountAsString,vl-2,0,','),vl-4,0,','),vl-6,0,','),vl-8,0,','),vl-10,0,',')
ELSE NULL END
FROM (SELECT
vl = CAST(1+LOG10(ABS(@amount)) AS INT),
AmountAsString = CONVERT(VARCHAR(50),ABS(@amount))
) d
---------------------------------------------------------------------------------------
SELECT FormattedAmount = CASE SIGN(@amount) WHEN -1 THEN '-' ELSE '' END +
COALESCE(x5.StrAmount, x4.StrAmount, x3.StrAmount, x2.StrAmount, x1.StrAmount, d.StrAmount)
FROM (SELECT
vl = CAST(1+LOG10(ABS(@amount)) AS INT),
StrAmount = CONVERT(VARCHAR(50),ABS(@amount))
) d
CROSS APPLY (SELECT StrAmount = CASE WHEN d.vl > 3 THEN STUFF( d.StrAmount,vl- 2,0,',') END) x1
CROSS APPLY (SELECT StrAmount = CASE WHEN d.vl > 5 THEN STUFF(x1.StrAmount,vl- 4,0,',') END) x2
CROSS APPLY (SELECT StrAmount = CASE WHEN d.vl > 7 THEN STUFF(x2.StrAmount,vl- 6,0,',') END) x3
CROSS APPLY (SELECT StrAmount = CASE WHEN d.vl > 9 THEN STUFF(x3.StrAmount,vl- 8,0,',') END) x4
CROSS APPLY (SELECT StrAmount = CASE WHEN d.vl > 11 THEN STUFF(x4.StrAmount,vl-10,0,',') END) x5
---------------------------------------------------------------------------------------
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 22, 2013 at 2:32 pm
sqlbi.vvamsi (1/22/2013)
this should sufficedeclare @mn money=-1000000
select replace(CONVERT(varchar(50),@mn,1),'.00','') as mn
No, that's "Western" format. The OP requires Indian format. This Wiki article gives a good explanation.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply