AK SQL AK SQL - 4 months ago 12
SQL Question

SQL CAST or Convert Function

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

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