将存储为文本数据类型的数字转换为 int [英] Convert number stored as text data type to 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:

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

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

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.

推荐答案

这似乎有效:CONVERT(INT, CONVERT(VARCHAR(MAX),myText))

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

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).

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

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

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.

这篇关于将存储为文本数据类型的数字转换为 int的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！