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.

Accepted Answer

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.

Convert number stored as text data type to int

Solution

Problem