Converting empty varchar to numeric

Dont know why but try this it should work investigate further why it will allow you to do this way and if you find anything on this do update us.

SELECT CONVERT(numeric(18,2),CAST(' ' AS INT))

Result

(No column name)
0.00

I have been looking online for some explanation only found this Link have a look still not very clear about this behaviour.

For,DECLARE @VAL1 float;

while executing this statement,

select convert(numeric(18,2),(case when 2=1 then @VAL1 else @VAL end ));

Sql server internally converts @VAL to Float(datatype of @VAL1) then compare gives you the output as zero.

DECLARE @VAL nvarchar(10);
DECLARE @VAL1 float;
set @VAL=' ';
set @VAL1=12.123;
select @VAL,@VAL1
select CONVERT(float,@VAL)--done by sql server internally 
select convert(numeric(18,2),(case when 2=1 then @VAL1 else @VAL end ));

but,for DECLARE @VAL1 numeric(18,2)

it actually gets error at sql server internal conversion.

DECLARE @VAL nvarchar(10);
DECLARE @VAL1 numeric(18,2);
set @VAL=' '; 
set @VAL1=12.123;
select @VAL,@VAL1
select CONVERT(numeric(18,2),@VAL)--at this point,sql sever unabled to convert @VAL to datatype Numeric
select convert(numeric(18,2),(case when 2=1 then @VAL1 else @VAL end ));

Not sure if you need it to also work with non-blank values

Declare @myValue varchar(10)
SET @myValue = ' '

SELECT 
    CASE 
        WHEN @myValue <> ' ' THEN 
            CONVERT(numeric(18,2), @myValue) 
        ELSE '0.00' 
    END

Outputs 0.00

Declare @myValue varchar(10)
SET @myValue = '3.2'

SELECT 
    CASE 
        WHEN @myValue <> ' ' THEN 
            CONVERT(numeric(18,2), @myValue) 
        ELSE

Outputs 3.20

http://blog.sqlauthority.com/2007/07/07/sql-server-convert-text-to-numbers-integer-cast-and-convert/

 SELECT CAST(YourVarcharCol AS INT) FROM Table
 SELECT CONVERT(INT, YourVarcharCol) FROM Table

Thanks All!!

I found the root cause. I have changed a column datatype float to numeric which related to the converted value.

Ex,

DECLARE @VAL nvarchar(10);
DECLARE @VAL1 float;
set @VAL=' ';
set @VAL1=12.123;
select convert(numeric(18,2),(case when 2=1 then @VAL1 else @VAL end ));

It Results

0.00

Changing @VAL1 datatype to numeric

DECLARE @VAL nvarchar(10);
DECLARE @VAL1 numeric(18,2);
set @VAL=' '; 
set @VAL1=12.123;
select convert(numeric(18,2),(case when 2=1 then @VAL1 else @VAL end ));

It Results the error, 'Error converting data type nvarchar to numeric'.

Will be welcomed if anyone explain whats really happening here.

declare @value varchar(10)=''

select case when ISNUMERIC(@value)=1 then convert(numeric(18,2),@value) else convert(numeric(18,2),0) end

So if @value ='' result will be 0.00. also let say if value is 6 result will be 6.00

Hi please take a look and try this

declare @xx as varchar(10)
set @xx = ''

select case when @xx ='' then convert(numeric(18,2),'0.00') 
       else convert(numeric(18,2),@xx) end as test

Thanks

You can use TRY_CAST()

Syntax: TRY_CAST ( expression AS data_type [ ( length ) ] )

Use TRY_PARSE ( string_value AS data_type [ USING culture ] ) in your case : select TRY_PARSE(' ' as NUMERIC)

Remarks : Use TRY_PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value.

TRY_PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR).