How to generate data in lakhs format in SQL Server 2008

• manibad

  SSCommitted

  Points: 1556

  More actions

  September 5, 2013 at 12:37 pm

  #277656

  Hi,

  I have a issue where we got to report money column in string in the format 1,00,000.00

  Example..

  Input-100000.00 My required output is 1,00,000.00

  Input-10000.00 My required output is 10,000.00

  Can anyone please help me on this.

  Thanks in advance.

• Koen Verbeeck

  SSC Guru

  Points: 259085

  More actions

  September 5, 2013 at 12:46 pm

  #1647814

  How do you report?

  If you use Reporting Services, this article explains number formatting:

  Formatting Numbers [SSRS][/url]

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• manibad

  SSCommitted

  Points: 1556

  More actions

  September 5, 2013 at 1:21 pm

  #1647826

  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.

• Koen Verbeeck

  SSC Guru

  Points: 259085

  More actions

  September 5, 2013 at 1:46 pm

  #1647835

  manibad (9/5/2013)

  ---

  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.

  Formatting should be done in the visualization layer, in this case Informatica.

  You could always convert the number to a string and place commas where they are needed.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  September 5, 2013 at 2:40 pm

  #1647843

  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.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• manibad

  SSCommitted

  Points: 1556

  More actions

  September 5, 2013 at 3:45 pm

  #1647866

  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.

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  September 5, 2013 at 6:28 pm

  #1647899

  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 re-use 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 N-scale < 5

  THEN ''

  -- the last/first comma goes here

  WHEN N-scale = 5

  THEN ','

  -- capture every second digit here

  WHEN (N-scale) % 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 pre-existing 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-2 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
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]