将存储为文本数据类型的数字转换为int

I have a database that is a result of an import. The database is a deliverable, I did not do the import myself, nor do I have access to the original data to do it myself. That being said, there is an integer value that was imported to a text datatype. All of the stored values are valid integers. I keep getting:

我有一个导入的数据库。数据库是可交付的,我自己没有进行导入,也没有自己访问原始数据。话虽如此,有一个整数值导入到text数据类型。所有存储的值都是有效的整数。我一直在:

Explicit conversion from data type text to int is not allowed.

if I try to change the field data type in the table. I have also created a new INT field in the table and tried to update it based upon the value in the TEXT field, but I receive the same error. Lastly I tried to create a new table and tried to insert the old values but cannot convert or cast to the int successfully.

如果我尝试更改表中的字段数据类型。我还在表中创建了一个新的INT字段,并尝试根据TEXT字段中的值更新它,但是我收到了同样的错误。最后,我尝试创建一个新表并尝试插入旧值但无法成功转换或转换为int。

3 个解决方案

#1

This seems to work: CONVERT(INT, CONVERT(VARCHAR(MAX),myText))

这似乎工作:CONVERT(INT,CONVERT(VARCHAR(MAX),myText))

Edit:

I'm not totally sure of what's the best choice for the inner conversion... Choosing either VARCHAR(N) with N > 10 or VARCHAR(MAX) has the advantage of not preventing an overflow by truncating (assuming the overflow is the preferred behavior in that case).

我不完全确定内部转换的最佳选择是什么...选择N> 10或VARCHAR(MAX)的VARCHAR(N)具有不通过截断防止溢出的优点(假设溢出是首选那种情况下的行为)。

Also, the conversion to INT seems to treat leading spaces as zero. So VARCHAR(MAX) reduces the chance of erroneously getting zero. E.g.:

此外,转换为INT似乎将前导空格视为零。所以VARCHAR(MAX)减少了错误地获得零的机会。例如。:

CREATE TABLE #foo ( bar TEXT )

INSERT INTO #foo
VALUES ('                                                 10')

SELECT CONVERT (INT, CONVERT(VARCHAR(MAX),bar)) FROM #foo -- 10
SELECT CONVERT (INT, CONVERT(VARCHAR(10),bar)) FROM #foo -- 0

Probably the best thing is to do some validation to make sure the input meets whatever your requirements are.

可能最好的做法是进行一些验证,以确保输入符合您的要求。

#2

TextValue to Int direct Conversion is not possible, so

TextValue到Int直接转换是不可能的,所以

1. convert TextValue to varchar(max)

将TextValue转换为varchar(max)

2. varchar(max) to int or bigint

varchar(max)to int或bigint

Example :

convert(int, (convert( varchar(max),'TextValue'))) as ColumnName

#3

You need to convert text type to varchar(x), after that you can cast or convert to int. To avoid double convert i prefer cast.

您需要将文本类型转换为varchar(x),之后您可以转换或转换为int。为避免双重转换,我更喜欢演员。

CAST(CONVERT(VARCHAR(50),CONFIG_VALUE) AS INT)

Full example:

DECLARE @age int;

SET @age = (SELECT CAST(CONVERT(varchar(50),@yourTextVarHere) AS INT))
SELECT @age