I am using a case statement to remove the '-' from a negative number and place a '(' there instead. 

The negative side of the CASE statement is as follows:

REPLACE(CONVERT(varchar, CONVERT(money, SUM(Begin_Balance)), 1), '-', '(') + ')'

The positive side is:

CONVERT(varchar, CONVERT(money, SUM(Begin_Balance)), 1)

The data output for the positive portion is 100% correct. Here is an example of the output I receive.

(18271.15)

(25000.00)

82,490.00

45,000.00

Why is the replace statement stripping my commas?

The style you've applied is being overridden by the implicit conversion to a plain old varchar. Those commas were never really there.

You should being doing your formatting in your presentation layer, not in the database.

Try this instead:

SELECT CASE WHEN @val < 0 THEN '(' + CONVERT(VARCHAR, ABS(@val),1) + ')' 
            ELSE CONVERT(VARCHAR, ABS(@val),1)
       END

That replace cannot remove comma's.  There is something else going on.  In order for us to figure out what is going on, please give us a script that we can copy and paste and run that duplicates your problem.  Something like the following, except, of course, the following does not show your problem.  The comma's are not eliminated.

declare @test table(myvalue decimal(10,2));
insert @test values
(-18271.15),
(-25000.00),
(82490.00),
(45000.00)

select 
  Case When myvalue < 0 Then REPLACE(CONVERT(varchar, CONVERT(money, myvalue), 1), '-', '(') + ')' 
    Else CONVERT(varchar, CONVERT(money, myvalue), 1) 
	End
from @test

Tom

• Marked as answer by beckham_msu 13 hours 11 minutes ago

Check:

DECLARE @Amount varchar(20) = '82,490.00';

SELECT  CONVERT(varchar, CONVERT(money, @Amount), 1),
REPLACE(CONVERT(varchar, CONVERT(money, @Amount), 1),',',SPACE(0));
-- 82,490.00	82490.00

I tried the ABS function. It gave me the exact same output as the previous way to do it. ABS() apparently removes the commas as well. 

I cannot access my presentation layer. The software is developed by someone else, it accepts a string and displays it in a list that our company uses. This software looks for views created in the database with a certain naming convention. It then looks at the view and displays the string(s) in the column with a specific alias. 

Thank you for the help. 

My incorrect statement was as follows:

REPLACE(CONVERT(varchar,CONVERT(money, @value), 1)))

I had a parentheses in the wrong place. It was completely skipping the money convert.

Such a simple solution. Thanks so much.  

\ABS() apparently removes the commas as well. 

No, it does not. It's a numeric function, and has nothing to do with strings.

>> I am using a case statement to remove the '-' from a negative number and place a '(' there instead. <<

So many horrible fundamental errors in one sentence! 


1.  CASE is an expression, not a statement. An expression returns a scalar value of a known data type, A statement changes the schema. 
2. In SQL, all display formatting is done in a presentation layer, never in the database.
3. We use the ANSI/ISO Standard  CAST()  and not the old Sybase/Unix CONVERT today
4. We use DECIMAL() and not the old Sybase/Unix MONEY. This thing does not work; it fail to do correct math; Google it! Do a data audit and see if you can go to jail for this. 
5. SQL is not COBOL. The COBOL language treats all data as strings. STOP DOING THAT IN SQL! This language uses abstraction in the database and the presentation does the job that your old PICTURE clause used to do. 

Can you get a 2-3 week intro course to SQL and modern programming form your company?