CODEWITHSUNDEEP
sqlreplacesubstring
xaria
xaria

Reputation: 842

SQL query to replace a substring

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

Upvotes: 0

Views: 1588

Answers (3)

Luke Stevenson
Luke Stevenson

Reputation: 10341

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
-

Upvotes: 2

Devin Garner
Devin Garner

Reputation: 1406

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

Upvotes: 0

armandomiani
armandomiani

Reputation: 730

use:

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

Upvotes: 0

Related Questions