如何将Nvarchar列转换为INT

I have a nvarchar column in one of my tables. Now I need to convert that column values to INT type..

我的一个表中有一个nvarchar列。现在我需要将该列值转换为INT类型..

I have tried using

我试过用

cast(A.my_NvarcharColumn as INT)

and

和

convert (int, N'A.my_NvarcharColumn')

When I ran my query I am getting errors like

当我运行我的查询时，我收到的错误就像

Conversion failed when converting the nvarchar value ' 23454542 ' to data type int.

将nvarchar值'23454542'转换为数据类型int时转换失败。

hi i am posting my entire code snippet

嗨，我发布我的整个代码片段

SELECT A.objID, name, des,  right(Replace(Ltrim(Replace(substring(my_nvarcharcolumn,1,9), '0', ' ')), ' ', '0'),10) AS new_nvarcharcolumn 
INTO #tmp1
FROM [database].[dbo].[tblname] AS A
INNER JOIN (SELECT * FROM [database].[dbo].tblname1 WHERE sourceID = 32) AS AI ON source = A.objID
INNER JOIN [database].[dbo].tblname2 AS I ON I.ObjectID = A.Source

SELECT MAX(m_dAddDate) AS date_Asof, dnum INTO #tmp2 FROM 
(SELECT * FROM [database].[dbo].tblname WHERE senior <> '' AND class = 'SSS') AS A
GROUP BY dnum

SELECT DISTINCT A.* INTO #tmp3 FROM #tmp1 AS A
INNER JOIN #tmp2 AS SD ON SD.dnum =cast(A.new_nvarcharcolumn as INT)
INNER JOIN database.[dbo].tbl4 AS M ON M.dnum = cast(A.new_nvarcharcolumn as INT)  AND SD.date_Asof = M.adddate

5 个解决方案

#1

CONVERT takes the column name, not a string containing the column name; your current expression tries to convert the string A.my_NvarcharColumn to an integer instead of the column content.

CONVERT获取列名，而不是包含列名的字符串;当前表达式尝试将字符串A.my_NvarcharColumn转换为整数而不是列内容。

SELECT convert (int, N'A.my_NvarcharColumn') FROM A;

should instead be

应该是

SELECT convert (int, A.my_NvarcharColumn) FROM A;

Simple SQLfiddle here.

这里简单的SQLfiddle。

#2

You can always use the ISNUMERIC helper function to convert only what's really numeric:

您始终可以使用ISNUMERIC辅助函数来仅转换真正数字的函数：

SELECT
     CAST(A.my_NvarcharColumn AS BIGINT)
FROM 
     A
WHERE
     ISNUMERIC(A.my_NvarcharColumn) = 1

#3

I know its Too late But I hope it will work new comers Try This Its Working ... :D

我知道它太晚但我希望它能为新来的人工作试试这个工作......：D

select 
  case 
      when isnumeric(my_NvarcharColumn) = 1 then 
              cast(my_NvarcharColumn AS int)
      else
              NULL
 end

AS 'my_NvarcharColumnmitter'
from A

#4

Your CAST() looks correct.

你的CAST（）看起来是正确的。

Your CONVERT() is not correct. You are quoting the column as a string. You will want something like

您的CONVERT（）不正确。您将该列引用为字符串。你会想要类似的东西

CONVERT(INT, A.my_NvarcharColumn)

** notice without the quotes **

**没有报价的通知**

The only other reason why this could fail is if you have a non-numeric character in the field value or if it's out of range.

这可能失败的唯一原因是，如果字段值中包含非数字字符，或者它超出范围。

You can try something like the following to verify it's numeric and return a NULL if it's not:

您可以尝试类似以下内容来验证它的数字，如果不是，则返回NULL：

SELECT
 CASE
  WHEN ISNUMERIC(A.my_NvarcharColumn) = 1 THEN CONVERT(INT, A.my_NvarcharColumn)
  ELSE NULL
 END AS my_NvarcharColumn

#5

If you want to convert from char to int, why not think about unicode number?

如果你想从char转换为int，为什么不考虑unicode数？

SELECT UNICODE(';') -- 59

This way you can convert any char to int without any error. Cheers.

这样，您可以将任何char转换为int而不会出现任何错误。干杯。

#1