SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 How to convert a datatype in a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

leoc50
Yak Posting Veteran

USA
54 Posts

Posted - 06/03/2013 :  17:59:02  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 06/03/2013 :  18:27:51  Show Profile  Visit russell's Homepage  Reply with Quote
Change 'NULL' to NULL
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 06/03/2013 :  18:30:30  Show Profile  Reply with Quote
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.


Edited by - James K on 06/03/2013 18:31:01
Go to Top of Page

leoc50
Yak Posting Veteran

USA
54 Posts

Posted - 06/04/2013 :  08:50:54  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000