convert NULL value in int column to nvarchar

Question

Post reply

convert NULL value in int column to nvarchar

Mad-Dog

SSCertifiable

Points: 6117
More actions
January 31, 2010 at 12:28 am

#138560

Hi,
can i convert a column that is int type to nvarchar where value is null in it?
THX

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

GURSETHI SSCommitted Points: 1912 More actions · Answer 1

Hi,

Please find below a small repro of this.

use test

go

create table hello (I int)

go

insert into hello values(1)

insert into hello values(2)

insert into hello values(3)

insert into hello values(null)

insert into hello values(5)

insert into hello values(null)

go

select * from hello

OUTPUT

*******

I

-----------

1

2

3

NULL

5

NULL

select cast(i as nvarchar(20)) from hello

or

select convert(nvarchar(20),i) from hello

OUTPUT

*******

--------------------

1

2

3

NULL

5

NULL

I Hope this is what you were trying to know if not please mail me.

Regards

GURSETHI

(sethigurpreet@yahoo.com)

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 2

now i want to update the null value to varchar like 'No Data'

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

If you want to change the columns data type have a look to BOL for ALTER TABLE and ALTER COLUMN.

If you want to change the output, not the stored data, surround GURSETHI's CAST/CONVERT with ISNULL or COALESCE.

Greets

Flo

Animal Magic SSChampion Points: 13964 More actions · Answer 4

use a case statement as well as the cast/convert.

case when <column> is null then 'No data' else cast(<column> as varchar(x)) End

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 5

how to use the case in the update statment for a value that is NULL in the column?

THX

Animal Magic SSChampion Points: 13964 More actions · Answer 6

Mad-Dog (1/31/2010)
how to use the case in the update statment for a value that is NULL in the column?
THX

update <tableA>

set <column> = case when B.<column> is null then 'No Data' else cast(<column> as nvarchar(x)) End

from <tableA>

join <tableB> on A.x = B.y

bhaskar.ba SSC Journeyman Points: 97 More actions · Answer 7

bhaskar.ba

SSC Journeyman

Points: 97

January 31, 2010 at 11:44 pm

#1111437

select isnull(i,'NoData') from hello

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 8

Animal Magic (1/31/2010)
Mad-Dog (1/31/2010)
how to use the case in the update statment for a value that is NULL in the column?
THX
update <tableA>
set <column> = case when B.<column> is null then 'No Data' else cast(<column> as nvarchar(x)) End
from <tableA>
join <tableB> on A.x = B.y

it's not working.

i get this error - Conversion failed when converting the nvarchar value 'No Data' to data type int.

Animal Magic SSChampion Points: 13964 More actions · Answer 9

is the field your updating an int field? if so you wont be able to put 'No data' into it. Post the create table scripts for both your tables and tell me what your trying to update.

nigel. SSChampion Points: 11636 More actions · Answer 10

Mad-Dog (2/1/2010)
it's not working.
i get this error - Conversion failed when converting the nvarchar value 'No Data' to data type int.

It won't work, your column is defined as an integer, and your trying to put character data in it!!! :w00t:

If you want to do this you'll need to change the column type (which was mentioned in an earlier post). But I don't think that's really what you want to do.

If you could explain the reasoning behind your question in more detail then someone may be able to help.

Nigel
How to post questions

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 11

yes I'm trying to update an int column to put varchar character.

i know that i can't update this column,but i think maybe there is something to do.

by the way how NULL can be insert into INT column?

nigel. SSChampion Points: 11636 More actions · Answer 12

Good, glad you understand that you can't update an integer column with a character value.

In general, any column can contain a NULL unless specifically prevented in the table definition (by specifying 'NOT NULL').

If you want to see the text 'No data' in place of the NULL in the results of a SELECT then you need to use, ISNULL or COALESCE, or even CASE (I think this was mentioned earlier).

Could you give further details on what you are going to do with this data. Display in a web page or in a report or what?

Nigel
How to post questions

Animal Magic SSChampion Points: 13964 More actions · Answer 13

Mad-Dog (2/1/2010)
yes I'm trying to update an int column to put varchar character.

By definition you cant. No amount of convert/case/coalesce will ever get this work.

Mad-Dog (2/1/2010)
i know that i can't update this column,but i think maybe there is something to do.

Like what? Why dont you put a -1 in if the value from the other table is null?

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 14

thanks all for your answers.

I'll try different approach.

p.s - i'm workig on a html report that sent from sql.