CASE with CHARINDEX and CONVERT I have a column of characters in SQL Server 2014 that I have to convert to numeric and give them the opposite value (- to + and + to -).
The data looks like this:

 -0000000000002999                                                                   
000000000000002999
000000000000002459
 -0000000000002459
000000000000000437


I need it to look like this:

29.99
-29.99
-24.59
24.59
-4.37


This is the query i'm trying to use but it makes them all positive!
select

CASE
  WHEN CHARINDEX('-', o.PMT_AMOUNT) &gt; '0'
     THEN CONVERT(numeric(18), Right(o.PMT_AMOUNT,17))* -.01 
  ELSE CONVERT(numeric(18), (o.PMT_AMOUNT)) * .01 
END from OPS_PMT_RECON_CONV o
Here is a more concise way to write this query without conditionals (SQLfiddle demo, but I guessed on the data type):

SELECT 
  PMT_AMOUNT = -1 * CONVERT(NUMERIC(20,2), STUFF(o.PMT_AMOUNT,LEN(o.PMT_AMOUNT)-1,0,'.'))
FROM dbo.OPS_PMT_RECON_CONV AS o;


Or even simpler, as @ypercube suggests:

SELECT 
  PMT_AMOUNT = -0.01 * CONVERT(NUMERIC(20,2), o.PMT_AMOUNT)
FROM dbo.OPS_PMT_RECON_CONV AS o;


A couple of suggestions:


Store the number in the database, instead of this awkward string representation. What is the point of persisting the number this way, even if you can't control how it comes to you? Converting to a number first, and storing it that way, will prevent you from storing junk in your database and end users getting burned for it.
Specify both precision and scale when defining numeric. This post is primarily about varchar, but applies equally to other types that accept parameters.
Always specify the schema.
Always terminate statements with semi-colons.