Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to generate data in lakhs format in SQL Server 2008


How to generate data in lakhs format in SQL Server 2008

Author
Message
manibad
manibad
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16443 Visits: 13202
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
manibad
manibad
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16443 Visits: 13202
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8506 Visits: 18115
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
manibad
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
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.
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2268 Visits: 7824
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


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




  • 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

  • manibad
    manibad
    SSC Journeyman
    SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

    Group: General Forum Members
    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 alotSmile
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search