none
Adding parentheses to only the negative numbers in sql column RRS feed

  • Question

  • I need to know how I can add parentheses  only to the negative numbers in my column.

    So in my select list If I have this

    Select (v.totalRemarketingCredits  - cs.ServiceFee) As TotalNetCostProceeds 

    from Maintable v inner join Customservice cs On v.id =cs.id


    How do I how to concatenate parentheses only around the negative numbers?

    So im looking for this

    TotalNetCostProceeds 

    100

    200

    (-1500)

    600

    (-445)

    Thanks

    Friday, December 20, 2019 9:29 PM

Answers

  • format(thevalue,'0;(-0)') 

    Select format(v.totalRemarketingCredits  - cs.ServiceFee,'0;(-0)') As TotalNetCostProceeds 
    from Maintable v inner join Customservice cs On v.id =cs.id
    

    • Marked as answer by KingRBG2009 Monday, December 23, 2019 4:29 PM
    Monday, December 23, 2019 2:57 PM
    Moderator

All replies

  • I think you probably better off doing this in application layer.

    DECLARE @table TABLE(snot INT)
    
    INSERT INTO @table
    (
        snot
    )
    VALUES
    (10 ),( -100)
    
    
    SELECT CASE WHEN snot <0 THEN '('+CAST(snot AS VARCHAR(20))+')' ELSE CAST(snot AS VARCHAR(20)) end AS [Value] FROM @table


    Hope it Helps!!

    Friday, December 20, 2019 9:44 PM
  • DECLARE @T TABLE (
    	totalRemarketingCredits int,
    	ServiceFee int
    );
    
    INSERT INTO @T VALUES (10, 5), (5, 10);
    
    SELECT CASE 
                WHEN totalRemarketingCredits - ServiceFee < 0 THEN '(' + CAST(totalRemarketingCredits - ServiceFee AS varchar(10)) + ')' 
    			ELSE CAST(totalRemarketingCredits - ServiceFee AS varchar(10)) 
           END AS TotalNetCostProceeds
    FROM @T;


    A Fan of SSIS, SSRS and SSAS

    Friday, December 20, 2019 9:47 PM
  • Hi,

    Select format(v.totalRemarketingCredits  - cs.ServiceFee,'0;(0)') As TotalNetCostProceeds 
    from Maintable v inner join Customservice cs On v.id =cs.id

    Mark as answer if it helps. Thanks.


    • Edited by Soumen Barua Saturday, December 21, 2019 7:15 AM
    Saturday, December 21, 2019 7:11 AM
  • Hi KingRGB2009,

    --import data
    create table Maintable ( id int,totalRemarketingCredits int )
    insert into Maintable values(1,1100),(2,1200),(3,100),(4,1000),(5,5)
    --import data
    create table Customservice( id int,ServiceFee int)
    insert into Customservice values(1,1000),(2,1000),(3,1600),(4,400),(5,450)
    --the query
    Select 
     case when (v.totalRemarketingCredits  - cs.ServiceFee) <0
          then concat( '(', cast(v.totalRemarketingCredits - cs.ServiceFee AS varchar(100)),')')
         else cast(v.totalRemarketingCredits - cs.ServiceFee AS varchar(100))
         end as TotalNetCostProceeds
    from Maintable v inner join Customservice cs On v.id =cs.id

    If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue. 

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, December 23, 2019 2:28 AM
  • format(thevalue,'0;(-0)') 

    Select format(v.totalRemarketingCredits  - cs.ServiceFee,'0;(-0)') As TotalNetCostProceeds 
    from Maintable v inner join Customservice cs On v.id =cs.id
    

    • Marked as answer by KingRBG2009 Monday, December 23, 2019 4:29 PM
    Monday, December 23, 2019 2:57 PM
    Moderator