问题描述
我有一个导入结果的数据库.数据库是可交付的,我没有自己做导入,也没有访问原始数据的权限来自己做.也就是说,有一个整数值被导入到文本数据类型中.所有存储的值都是有效的整数.我不断得到:
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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!