Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need Alternate Solution Expand / Collapse
Author
Message
Posted Sunday, January 20, 2013 10:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 15, 2014 10:37 AM
Points: 46, Visits: 182
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, ','))
SET @index = @index + 2
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
Post #1409360
Posted Monday, January 21, 2013 12:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 26, 2014 2:50 AM
Points: 1,255, Visits: 709
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.

http://www.sqlstairways.com
Post #1409375
Posted Monday, January 21, 2013 12:58 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:08 AM
Points: 884, Visits: 2,418
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, ','))
SET @index = @index + 2
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
Post #1409384
Posted Monday, January 21, 2013 5:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:54 AM
Points: 6,750, Visits: 13,897
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

---------------------------------------------------------------------------------------




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1409500
Posted Tuesday, January 22, 2013 12:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 4:51 PM
Points: 54, Visits: 368
this should suffice

declare @mn money=-1000000
select replace(CONVERT(varchar(50),@mn,1),'.00','') as mn
Post #1410231
Posted Tuesday, January 22, 2013 2:32 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:47 AM
Points: 1,031, Visits: 6,731
sqlbi.vvamsi (1/22/2013)
this should suffice

declare @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.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




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
Post #1410268
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse