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

How to generate data in lakhs format in SQL Server 2008 Expand / Collapse
Author
Message
Posted Thursday, September 5, 2013 12:37 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 91, Visits: 143
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.
Post #1491912
Posted Thursday, September 5, 2013 12:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
How do you report?
If you use Reporting Services, this article explains number formatting:
Formatting Numbers [SSRS]




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1491915
Posted Thursday, September 5, 2013 1:21 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 91, 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.
Post #1491928
Posted Thursday, September 5, 2013 1:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1491938
Posted Thursday, September 5, 2013 2:40 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 3,634, Visits: 8,146
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1491950
Posted Thursday, September 5, 2013 3:45 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 91, 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.
Post #1491976
Posted Thursday, September 5, 2013 6:28 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:53 PM
Points: 1,780, Visits: 5,750
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 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 Addin
  • MMNose Addin


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

  • Post #1492010
    Posted Friday, September 6, 2013 2:45 PM
    SSC Journeyman

    SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

    Group: General Forum Members
    Last Login: Thursday, July 17, 2014 12:46 PM
    Points: 91, 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 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 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:)
    Post #1492427
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse