InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 02:21:01 GMT20http://www.sqlservercentral.com/Forums/Topic1409360-392-1.aspx[quote][b]sqlbi.vvamsi (1/22/2013)[/b][hr]this should sufficedeclare @mn money=-1000000select replace(CONVERT(varchar(50),@mn,1),'.00','') as mn[/quote]No, that's "Western" format. The OP requires Indian format. [url=http://en.wikipedia.org/wiki/Indian_rupee]This Wiki article [/url]gives a good explanation.Tue, 22 Jan 2013 14:32:29 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1409360-392-1.aspxthis should sufficedeclare @mn money=-1000000select replace(CONVERT(varchar(50),@mn,1),'.00','') as mnTue, 22 Jan 2013 12:45:19 GMTsqlbi.vvamsihttp://www.sqlservercentral.com/Forums/Topic1409360-392-1.aspxHave you considered using a more efficient inline table-valued function instead? Here are a couple of ideas:[code="sql"]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---------------------------------------------------------------------------------------[/code]Mon, 21 Jan 2013 05:13:05 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1409360-392-1.aspxI 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)[code="sql"]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 [/code]Mon, 21 Jan 2013 00:58:38 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1409360-392-1.aspxyou 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.Mon, 21 Jan 2013 00:27:50 GMTGaurang-Patelhttp://www.sqlservercentral.com/Forums/Topic1409360-392-1.aspxHi,I Created Function For Indian Money Format [code="sql"]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 [/code][u][b]1.Requirement[/b][/u]Its Working Fine in Positive Amount[i]For Example:[u][/u][/i][code="sql"]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[/code]But Its not Working Fine in Negative Amount[i]For Example:[u][/u][/i][code="sql"]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[/code]How To Solve The Issue...[u][b]2.Requirement[/b][/u]Is it Possible to Return Type as Float or Numeric Type...Please Give Me The Solution As Soon as PossibleThanks & Regards,D.SaravananSun, 20 Jan 2013 22:51:33 GMTsarwaanmca