Convert number stored as text data type to int

Question

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.

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.

Answer 1

This seems to work: 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).

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.