CODEWITHSUNDEEP
sqlsql-server
Slim
Slim

Reputation: 1744

replace or remove characters and then cast to decimal

I need to remove or replace an unwanted character (%) which is at the end of a string and then to cast the string to a decimal, because I need to get the sum of 2 fields named discount1 and discount2

The last combination I tried is:

replace ((p16.pa_value,'%','')CAST (p16.pa_value AS DECIMAL (3,0))) as discount2,

It seems to be super wrong as the CAST is inside of the replace statement

Upvotes: 4

Views: 17983

Answers (2)

Kamila
Kamila

Reputation: 43

OK, I don't know why do you have three digits after decimal: DECIMAL (3,0) because if you convert to decimal it should be: decimal (10; how many zeros after ,) - more info here

Try this:

(cast(replace(p16.pa_value, '%','')  as decimal (10,2))) as discount

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24086

try this:

CAST (replace (p16.pa_value,'%','') AS DECIMAL (3,0)) as discount2

Upvotes: 7

Related Questions