How to convert a datatype in a table

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

General SQL Server Forums

New to SQL Server Administration

How to convert a datatype in a table

Author

Topic

leoc50
Yak Posting Veteran

54 Posts

Posted - 2013-06-03 : 17:59:02

What's the best way to convert a 'nvarchar(255)' to 'int' (or any numeric type) in a populated SQL table. I tried several ways but kept getting:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'NULL' to data type int.
The statement has been terminated.

My basic command is:
ALTER TABLE dbo.abc
alter column [col1] int NULL
[with many variations and casting/convert embedded]

Any help would be greatly appreciated!!

Thanks!!
lc

- lec

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-06-03 : 18:27:51

Change 'NULL' to NULL

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-03 : 18:30:30

quote:
Originally posted by leoc50

What's the best way to convert a 'nvarchar(255)' to 'int' (or any numeric type) in a populated SQL table. I tried several ways but kept getting:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'NULL' to data type int.
The statement has been terminated.

My basic command is:
ALTER TABLE dbo.abc
alter column [col1] int NULL
[with many variations and casting/convert embedded]

Any help would be greatly appreciated!!

Thanks!!
lc

- lec

That sounds like you have the string NULL (rather than NULL values) in col1. See if that is the case using this query:

SELECT col1 FROM dbo.abc WHERE col1 = 'NULL'

If you see any rows, replace them with a null first

UPDATE dbo.abc SET col1 = NULL WHERE col1 = 'NULL'

Once you do that, try the alter column command again. Be sure to include the where clause as I have shown in your update statement, or you will end up wiping out all the data in the table.

leoc50
Yak Posting Veteran

54 Posts

Posted - 2013-06-04 : 08:50:54

Thank ya'll
Your responses reassured me I wasn't in the right path; so digging more on the values (i.e. select distinct(col1) ....) I found that there were other charaters I assumed weren't there...yeah I know, 'Never Assume'
Anyway, I had values like
['-', '?????', '1', '1,089', '1,322', '1.5', '1.62']
so for testing only I updated those to the same value just for testing, and once that I'm able to change the column to 'int' or even 'real'.

Thanks again!!
lc

- lec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources