Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Need Alternate Solution Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, January 20, 2013 10:51 PM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, November 13, 2013 7:23 AM Points: 46, Visits: 181
 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.RequirementIts Working Fine in Positive AmountFor Example:`select dbo.fn_Format_Money_Indian (1000) -- Result is 1,000select dbo.fn_Format_Money_Indian (10000) -- Result is 10,000select dbo.fn_Format_Money_Indian (100000) -- Result is 1,00,000select dbo.fn_Format_Money_Indian (1000000) -- Result is 10,00,000`But Its not Working Fine in Negative AmountFor Example:`select dbo.fn_Format_Money_Indian (-10000) -- Result is -,10,000 But Reqired Format is -10,000select dbo.fn_Format_Money_Indian (-1000000) -- Result is -,10,00,000 But Reqired Format is -10,00,000select 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.RequirementIs it Possible to Return Type as Float or Numeric Type...Please Give Me The Solution As Soon as PossibleThanks & Regards,D.Saravanan
Post #1409360
 Posted Monday, January 21, 2013 12:27 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, December 05, 2013 3:52 AM Points: 1,246, Visits: 629
 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! Group: General Forum Members Last Login: Wednesday, November 20, 2013 2:57 AM Points: 854, Visits: 2,292
 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 Group: General Forum Members Last Login: Friday, December 06, 2013 7:32 AM Points: 6,274, Visits: 12,089
 Have you considered using a more efficient inline table-valued function instead? Here are a couple of ideas:`DECLARE @amount MONEYSET @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 ENDFROM (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) x1CROSS APPLY (SELECT StrAmount = CASE WHEN d.vl > 5 THEN STUFF(x1.StrAmount,vl- 4,0,',') END) x2CROSS APPLY (SELECT StrAmount = CASE WHEN d.vl > 7 THEN STUFF(x2.StrAmount,vl- 6,0,',') END) x3CROSS APPLY (SELECT StrAmount = CASE WHEN d.vl > 9 THEN STUFF(x3.StrAmount,vl- 8,0,',') END) x4CROSS 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1409500
 Posted Tuesday, January 22, 2013 12:45 PM
 Valued Member Group: General Forum Members Last Login: Tuesday, December 03, 2013 2:38 PM Points: 54, Visits: 297
 this should sufficedeclare @mn money=-1000000select replace(CONVERT(varchar(50),@mn,1),'.00','') as mn
Post #1410231
 Posted Tuesday, January 22, 2013 2:32 PM
 SSC Eights! Group: General Forum Members Last Login: Today @ 6:51 AM Points: 959, Visits: 4,564
 sqlbi.vvamsi (1/22/2013)this should sufficedeclare @mn money=-1000000select replace(CONVERT(varchar(50),@mn,1),'.00','') as mnNo, 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

 Permissions

 Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.