0
SET @newNum = IF(
    RIGHT("-600.00",1) REGEXP '^[-]' = '-',
    REPLACE("-600.00",",",""),
    -1*REPLACE(REPLACE("-600.00",",",""),"-","")
);

I keep getting a truncated incorrect DOUBLE value: '-' when executing this line, anyone know what is causing this ?

Salman A
  • 262,204
  • 82
  • 430
  • 521
Ashley06
  • 29
  • 1
  • 2
  • 7
  • `RIGHT("-600.00",1) REGEXP '^[-]' = '-'` doesn't make sense logically (although syntactically MySQL will parse this as something). Another example why showing sample data and desired results is more useful than code that doesn't work. – Gordon Linoff Mar 12 '15 at 11:48
  • Basically if someone puts "600.00-" or "-600.00" it will output "-600". – Ashley06 Mar 12 '15 at 11:52
  • . . That's nice. What do you want the code to do? What does the data look like? Generally, a conditional expression with two comparisons (in this case `regexp` and `=`) doesn't make sense. – Gordon Linoff Mar 12 '15 at 11:53
  • The code needs to format numbers if they are given in an incorrect format (ex: "600.00- --> "-600.00"). I don't know what you mean by what the data looks like ? What would you suggest instead of `regexp` and `=`. – Ashley06 Mar 12 '15 at 11:58

1 Answers1

0

REGEXP returns 1 or 0 depending on whether there is a match or not. Comparing the result with - is incorrect, you can do this instead:

DECLARE @oldNum VARCHAR(10);
DECLARE @newNum VARCHAR(10);
SET @oldNum = '600.00-';
SET @newNum = IF(
    RIGHT(@oldNum, 1) = '-',
    CONCAT('-', REPLACE(REPLACE(@oldNum, ',', ''), '-', '')),
                        REPLACE(@oldNum, ',', '')
);
SELECT @oldNum, @newNum
Salman A
  • 262,204
  • 82
  • 430
  • 521