converting a null nvarchar(50) column to zero

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

converting a null nvarchar(50) column to zero

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-12 : 01:25:36

how can i convert ad a part of an inner join a column which i want to comopare and convert it from nvarchar(50) with the value null to zero!
i tried ::
cast (numeric,isnull(col1,0))
but i recive : error converrting nvarchar to numeric!
thnaks i nadvacen
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Kristen
Test

22859 Posts

Posted - 2006-02-12 : 02:20:42

cast (numeric,isnull(col1,'0'))

maybe?

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-13 : 01:47:44

quote:
Originally posted by Kristen

cast (numeric,isnull(col1,'0'))

maybe?

Kristen

or may be

Convert(numeric,isnull(col1,'0'))

or

cast (isnull(col1,'0') as numeric)

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-13 : 01:56:36

why do i need '0' ?
i want to give a null column the zero value not the string '0'

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-13 : 05:04:00

quote:
Originally posted by pelegk2

why do i need '0' ?
i want to give a null column the zero value not the string '0'

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

But your col1 column is a nvarchar(50) not a numeric data type.
For those record where col1 is not null, your select statement will return nvarchar. And if you do this : cast (numeric,isnull(col1,0)); converting record with NULL value to 0, SQL Server will attempt to convert the other records (where col1 is not null) to numeric and will not be able to do so. And the result is error converrting nvarchar to numeric!

That's why you need to do this.

cast (numeric,isnull(col1,'0'))

EDIT : should be isnull(col1, 0)

Converting the null value of col1 to a character string.

----------------------------------
'KH'
everything that has a beginning has an end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-13 : 05:12:59

quote:
That's why you need to do this.

cast (numeric,isnull(col1,'0'))

As I told earlier, that should be
cast (isnull(col1,'0') as numeric)

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-13 : 05:38:18

Oops.. Sorry... should be isnull(col1, 0) or isnull(col1, '0')

See below codes for illustration

create table test
(
	id	int,
	data	varchar(50)
)

insert into test
select	1, 'this is a string'	union all
select	2, NULL	union all
select	3, '12345'

-- list all records
select * from test

-- this is OK
select 	isnull(data, 0)
from	test

-- This will have 'error converting data type varchar to numeric'
select 	convert (numeric, isnull(data, 0))
from	test

----------------------------------
'KH'
everything that has a beginning has an end

Kristen
Test

22859 Posts

Posted - 2006-02-13 : 10:33:32

"cast (isnull(col1,'0') as numeric)"

I got carried away cut-and-pasting to fix the original; VarChar/Numeric problem, and missed the other syntax error!

I never use CAST, only ever CONVERT, which didn't help matters

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-14 : 01:11:34

>>I never use CAST, only ever CONVERT, which didn't help matters

Yes it is more helpful if it deals with Dates

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources