Menu
How to convert a string to integer using SQL query on SQL Server 2005?
393
CAST and CONVERT can be used to convert a string to a number of any data type. For example, you can convert a string to a number of data type INTEGER. TO_DATE converts a formatted date string to a date integer. TO_TIMESTAMP converts a formatted date and time string to a standard timestamp.
SQL Server CAST() Function The CAST() function converts a value (of any type) into a specified datatype. Tip: Also look at the CONVERT() function.
You could use CAST or CONVERT:
SELECT CAST(MyVarcharCol AS INT) FROM Table SELECT CONVERT(INT, MyVarcharCol) FROM Table 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
