SQL CAST or Convert Function

Question

Hi i am new to SQL and need to convert the following cast statement from varchar(255) to decimal(6,2) i tried many options but couldn't achieve it

case when [ A 2 cost] = ' £-   ' then '0' else [ A 2 cost] end as Costing

the result of [A 2 Cost] in destination is decimal(6,2) datatype but in source it is varchar(255)

Answer 1

Try this

    case when [ A 2 cost] = ' £-   ' 
then 0.0 else cast([ A 2 cost] as decimal(5,2)) end as Costing

if the other columns contains £ in the beginning you need to clean it:

    case when [ A 2 cost] = ' £-   ' 
then 0.0 else cast(REPLACE([ A 2 cost],'£','') as decimal(5,2)) end as Costing