Converting empty varchar to numeric

Question

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.

I can't get `convert(numeric(18,2), '')` to work – Moho Nov 29 '13 at 15:22 — Moho, Nov 29 '13 at 15:22
Is there anything related to SQL Server Versions ? – janasoft Nov 29 '13 at 15:46 — janasoft, Nov 29 '13 at 15:46
i tried both 2005 and 2012 and both throw errors – Moho Nov 29 '13 at 15:48 — Moho, Nov 29 '13 at 15:48

score 1 · Answer 1 · edited May 23 '17 at 12:14

1

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.

edited May 23 '17 at 12:14

Community

1
1

answered Nov 29 '13 at 14:52

M.Ali

67,945
13
101
127

score 1 · Answer 2 · answered Dec 03 '13 at 07:59

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

score 0 · Answer 3 · answered Nov 29 '13 at 15:07

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

Suganthan Madhavan Pillai · Answer 4 · 2013-11-29T15:21:28.933

0

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

edited Nov 29 '13 at 15:21

answered Nov 29 '13 at 15:13

Suganthan Madhavan Pillai

5,495
9
51
84

score 0 · Answer 5 · answered Nov 29 '13 at 16:01

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.

score 0 · Answer 6 · answered Nov 29 '13 at 16:08

0

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

answered Nov 29 '13 at 16:08

huMpty duMpty

14,346
14
60
99

Angelo · Answer 7 · 2013-12-03T08:17:21.803

0

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

edited Dec 03 '13 at 08:17

answered Dec 03 '13 at 07:59

Angelo

335
4
10

score 0 · Answer 8 · answered Apr 11 '22 at 07:22

0

You can use TRY_CAST()

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

answered Apr 11 '22 at 07:22

Anas Khan

31
1
6

score -1 · Answer 9 · answered Aug 11 '21 at 09:26

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

Converting empty varchar to numeric

9 Answers9