How to convert a string to integer using SQL query on SQL Server 2005?
Add a comment
|
5 Answers
You could use CAST or CONVERT:
SELECT CAST(MyVarcharCol AS INT) FROM Table
SELECT CONVERT(INT, MyVarcharCol) FROM Table
answered Apr 8, 2009 at 7:13
Christian C. Salvadó
831k185 gold badges929 silver badges845 bronze badges
5 Comments
Chloe
How do I catch/prevent the exception when one of the fields is non-numeric? I would have expected it to convert to 0.
Chloe
Found it:
select CASE WHEN ISNUMERIC('x') = 1 THEN CAST('x' AS INT) ELSE 0 END
Curt
Just a word:
IsNumeric() can produce some perverse results. It will return TRUE for the string "-.", which will still cause an error when you try to cast it to a number.
Pratyush Dhanuka
IsNumeric will be true for strings with "-" only if the string is numeric for example "-5" or "-20", it will be false for strings like "5-", "-2-1". So if isNumeric() is true then conversion should not throw any exception
Arnaud D
Now with SQL Server 2016+ you have TRY_CAST() that returns null if cast is not possible
Starting with SQL Server 2012, you could use TRY_PARSE or TRY_CONVERT.
SELECT TRY_PARSE(MyVarcharCol as int)
SELECT TRY_CONVERT(int, MyVarcharCol)
1 Comment
shaune
Definitely not the right answer for the original question since it was in relation to SQL Server 2005, but since it is 2019 and fewer folks are strapped to such an old version of SQL Server, this answer is definitely helpful.
Also be aware that when converting from numeric string ie '56.72' to INT you may come up against a SQL error.
Conversion failed when converting the varchar value '56.72' to data type int.
To get around this just do two converts as follows:
STRING -> NUMERIC -> INT
or
SELECT CAST(CAST (MyVarcharCol AS NUMERIC(19,4)) AS INT)
When copying data from TableA to TableB, the conversion is implicit, so you dont need the second convert (if you are happy rounding down to nearest INT):
INSERT INTO TableB (MyIntCol)
SELECT CAST(MyVarcharCol AS NUMERIC(19,4)) as [MyIntCol]
FROM TableA
Comments
Try this one, it worked for me in Athena:
cast(MyVarcharCol as integer)
Comments
Use the below query:
select to_number('String') from dual;
1 Comment
Urasquirrel
'to_number' is not a recognized built-in function name