Convert nvarchar to bigint in Sql server 2008

sql sql-server-2008 nvarchar bigint
Hossein Moradinia picture Hossein Moradinia · Apr 29, 2011 · Viewed 60.6k times · Source

I want insert all rows of a table into another table, and I also want convert a nvarchar field into bigint, but when I use convert(bigint, col1) SQL Server shows an error:

Error converting data type nvarchar to bigint

How can I fix this problem?

Answer

marc_s picture marc_s · Apr 29, 2011

You could try to use ISNUMERIC to determine those rows that are indeed numeric:

UPDATE dbo.YourTable
SET BigIntColumn = CAST(NVarcharColumn AS BIGINT)
WHERE ISNUMERIC(NVarcharColumn) = 1

That would convert those rows that can be converted - the others need to be dealt with manually.

Related questions

Nvarchar and text are incompatible in the add operator

With SQL Server 2008, I'd like to add a variable (@wfID) into a text, like below: DECLARE @wfID uniqueidentifier SET @wfID = NEWID() '<META http-equiv="Content-Type" content="text/html; " /> <br><input type="button" value="" onclick="window.…

Read More
Converting Varchar to NVarchar?

I know when converting from nvarchar to varchar, some data will be lost/changed. However, are there any risks of data changing when converting a varchar data type to an nvarchar?

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More