13

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hossein Moradinia
  • 6,116
  • 14
  • 59
  • 85

4 Answers4

26

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • This will fail for decimals stored as strings: SELECT CAST('.1' AS BIGINT) WHERE ISNUMERIC('.1') = 1 – BD. Jan 05 '16 at 15:51
  • just to add to BD's comment, char(36) and chars 43-46, 48-57, 92, 128, 160, 162-165 all evaluate to 1 being numeric. The real kicker is in a where or join statement where you filter out all the non-numeric values logically first, at the end do a convert statement, and this error still occurs (ignores short circuit and evaluates whole line.) – Zachary Scott Aug 18 '16 at 20:54
0

You should convert bigint to nvarchar not vice versa cast(Other_Column_name as nvarchar) not cast (Column_Name as bigint)

Gamal Weshahy
  • 1
0

you can try this:

CAST(CAST(col1 as NUMERIC) as BIGINT)
AliNajafZadeh
  • 1,216
  • 2
  • 13
  • 22
0

Here I convert navrchar value of column into bigInt and than perform Addition of thos two columns find blow :

SELECT (CAST(col1 AS BIGINT) + CAST(col2 AS BIGINT)) AS TotalValue from tableName
ChrisMM
  • 8,448
  • 13
  • 29
  • 48
Mohit Lodhi
  • 1