

SSC Journeyman
Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 87,
Visits: 143


Hi, I have a issue where we got to report money column in string in the format 1,00,000.00 Example.. Input100000.00 My required output is 1,00,000.00 Input10000.00 My required output is 10,000.00
Can anyone please help me on this. Thanks in advance.




SSChampion
Group: General Forum Members
Last Login: Today @ 5:12 AM
Points: 13,271,
Visits: 10,149





SSC Journeyman
Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 87,
Visits: 143


Hi, We use Informatica for reporting the data and we are able to do it in Informatica. But I am unable to do it in SQL.




SSChampion
Group: General Forum Members
Last Login: Today @ 5:12 AM
Points: 13,271,
Visits: 10,149





Hall of Fame
Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 3,342,
Visits: 7,227


I agree with Koen when he says that formatting should be done in the visualization layer. But if the column type is money, then you can use CONVERT and a format code.
money and smallmoney Styles When expression is money or smallmoney, style can be one of the values shown in the following table. Other values are processed as 0.
Value Output 0 (default) No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98. 1 Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92. 2 No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.
For example:
SELECT CONVERT( varchar(15), MyColumn, 1)
Luis C. I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock
Forum Etiquette: How to post data/code on a forum to get the best help




SSC Journeyman
Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 87,
Visits: 143


Luis Cazares (9/5/2013)
I agree with Koen when he says that formatting should be done in the visualization layer. But if the column type is money, then you can use CONVERT and a format code. money and smallmoney Styles When expression is money or smallmoney, style can be one of the values shown in the following table. Other values are processed as 0.
Value Output 0 (default) No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98. 1 Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92. 2 No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.
For example: SELECT CONVERT( varchar(15), MyColumn, 1)
BY using the above we will be able to generate output like 100,000.00 and so on...but my output should be 1,00,000.00...and for this i am able to achieve it in informatica but my wish is to make it achievable in SQL.




SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 5:47 PM
Points: 1,781,
Visits: 5,666


Hi, this should help:
CREATE FUNCTION FormatLakhCrores (@number SQL_VARIANT,@conversion smallint) RETURNS TABLE WITH SCHEMABINDING AS = Function to format a number using the Lakh/Crores style = Written 06 Sept 2013 Mister Magoo = Permission granted for public reuse for any purpose except derision = Parameter @number : pass in a numeric/decimal/integer/money type = Parameter @conversion : pass in a suitable conversion style for the CONVERT function  if in doubt pass 0 (zero) (a value of 2 with the money data type will force 4 decimal places) = Returns a varchar value containing the formatted number RETURN
 the returned value is built up by stripping the number down and inserting commas where required, then concatenating the digits SELECT  because the comma insertion works from right to left, we need to reverse the result to see the required string REVERSE((  the case statement figures out where to insert a comma  we need a comma before the last three digits before the decimal point  but we are working from right to left, so they are the first three after the point.  subsequently we need a comma every two digits SELECT CASE  all digits after the last/first comma are in this group WHEN Nscale < 5 THEN ''  the last/first comma goes here WHEN Nscale = 5 THEN ','  capture every second digit here WHEN (Nscale) % 2 = 1 THEN ',' ELSE '' END  and grab the digit at this position in the string + SUBSTRING(string, LEN(string)  N+1, 1) FROM  Build an inline tally table which contains 49 numbers  more than enough for a decimal(38) ( SELECT ROW_NUMBER() OVER(ORDER BY @conversion)  the order by is irrelevant FROM (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) a7(N) ,(SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) b7(N) ) AS Tally49(N) CROSS APPLY  Use a cross apply construct to alias a couple of expressions ( SELECT  our plain formatted number with any preexisting commas stripped replace(convert(VARCHAR(1000), @number, @conversion),',','') AS string  figure where the decimal point is  if any is present ,CHARINDEX('.',reverse(replace(convert(VARCHAR(1000), @number, @conversion),',','')))1 ) x(string, scale)  only use as much of the Tally table as we need WHERE N< = len(string) ORDER BY N  and concatenate the results using for xml path() FOR XML PATH('') )) AS FormattedNumber
Use it like this for "money" :
declare @test money = 1000000000;
select FormattedNumber from FormatLakhCrores(@test,0)
Or from a table:
select some_column,FormattedNumber from SomeTable cross apply dbo.FormatLakhCrores(some_column,0)
MM
MMGrid AddinMMNose Addin
Forum Etiquette: How to post Reporting Services problemsForum Etiquette: How to post data/code on a forum to get the best help  by Jeff ModenHow to Post Performance Problems  by Gail Shaw




SSC Journeyman
Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 87,
Visits: 143


mister.magoo (9/5/2013)
Hi, this should help: CREATE FUNCTION FormatLakhCrores (@number SQL_VARIANT,@conversion smallint) RETURNS TABLE WITH SCHEMABINDING AS = Function to format a number using the Lakh/Crores style = Written 06 Sept 2013 Mister Magoo = Permission granted for public reuse for any purpose except derision = Parameter @number : pass in a numeric/decimal/integer/money type = Parameter @conversion : pass in a suitable conversion style for the CONVERT function  if in doubt pass 0 (zero) (a value of 2 with the money data type will force 4 decimal places) = Returns a varchar value containing the formatted number RETURN
 the returned value is built up by stripping the number down and inserting commas where required, then concatenating the digits SELECT  because the comma insertion works from right to left, we need to reverse the result to see the required string REVERSE((  the case statement figures out where to insert a comma  we need a comma before the last three digits before the decimal point  but we are working from right to left, so they are the first three after the point.  subsequently we need a comma every two digits SELECT CASE  all digits after the last/first comma are in this group WHEN Nscale < 5 THEN ''  the last/first comma goes here WHEN Nscale = 5 THEN ','  capture every second digit here WHEN (Nscale) % 2 = 1 THEN ',' ELSE '' END  and grab the digit at this position in the string + SUBSTRING(string, LEN(string)  N+1, 1) FROM  Build an inline tally table which contains 49 numbers  more than enough for a decimal(38) ( SELECT ROW_NUMBER() OVER(ORDER BY @conversion)  the order by is irrelevant FROM (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) a7(N) ,(SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) b7(N) ) AS Tally49(N) CROSS APPLY  Use a cross apply construct to alias a couple of expressions ( SELECT  our plain formatted number with any preexisting commas stripped replace(convert(VARCHAR(1000), @number, @conversion),',','') AS string  figure where the decimal point is  if any is present ,CHARINDEX('.',reverse(replace(convert(VARCHAR(1000), @number, @conversion),',','')))1 ) x(string, scale)  only use as much of the Tally table as we need WHERE N< = len(string) ORDER BY N  and concatenate the results using for xml path() FOR XML PATH('') )) AS FormattedNumber
Use it like this for "money" : declare @test money = 1000000000;
select FormattedNumber from FormatLakhCrores(@test,0)
Or from a table: select some_column,FormattedNumber from SomeTable cross apply dbo.FormatLakhCrores(some_column,0)
Thanks alot:)



