SQL query to replace a substring

Question

I wish to write a query which will replace the value0.00 with "-" in a column. The value should only be replaced if the value is 0.00 and not if 230.00

I did this query replace(SUBSTRING(cast(columname AS varchar(7)),0,7),'0.00','-') but it replaces 230.00 as 23-

Please help

Luke Stevenson · Accepted Answer · 2011-07-15 04:03:11Z

2

Assuming a table format like so:

`id` | `cost`
 1   | 230.00
 2   | 543.65
 3   | 0.00

Then a query of

SELECT CASE WHEN cost=0 THEN '-' ELSE cost END FROM table;

will return

230.00
543.65
-

answered Jul 15, 2011 at 4:03

Luke Stevenson

10.3k2 gold badges28 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

armandomiani · Accepted Answer · 2011-07-15 03:57:15Z

0

use:

if (SUBSTRING(cast(columname AS varchar(7)),0,7) = '0.00', '-', SUBSTRING(cast(columname AS varchar(7)),0,7))

answered Jul 15, 2011 at 3:57

armandomiani

7306 silver badges16 bronze badges

Comments

Devin Garner · Accepted Answer · 2011-07-15 03:57:43Z

0

CASE WHEN columname = 0 THEN '0.00' ELSE replace(SUBSTRING(cast(columname AS varchar(7)),0,7),'0.00','-') END

answered Jul 15, 2011 at 3:57

Devin Garner

1,41610 silver badges21 bronze badges

Collectives™ on Stack Overflow

SQL query to replace a substring

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related