CODEWITHSUNDEEP
sqlsql-server
janasoft
janasoft

Reputation: 163

Converting empty varchar to numeric

I am using SQL server 2008 R2 and Microsoft SQL Server Management Studio 0.50.2500.0.

In my Stored Procedure,

I am converting varchar to numeric(18,2).

select convert(numeric(18,2),' ')

It returned 0.00 when the value was ' ', which was required.

But now, its giving error 'Error converting data type varchar to numeric.'

Can anyone please tell me that what wrong I did ? or Which made this change?

Thanks in advance.

Upvotes: 4

Views: 15639

Answers (9)

Anas Khan
Anas Khan

Reputation: 31

You can use TRY_CAST()

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

Upvotes: 0

chetan chaudhari
chetan chaudhari

Reputation: 1

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).

Upvotes: -1

Angelo
Angelo

Reputation: 335

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

Upvotes: 0

Ram Das
Ram Das

Reputation: 358

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 ));

Upvotes: 1

huMpty duMpty
huMpty duMpty

Reputation: 14460

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

Upvotes: 0

janasoft
janasoft

Reputation: 163

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.

Upvotes: 0

Suganthan Madhavan Pillai
Suganthan Madhavan Pillai

Reputation: 5655

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

Upvotes: 0

Damon
Damon

Reputation: 3012

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

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

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.

Upvotes: 1

Related Questions