1. How can I CAST a string (nvarchar datatype) from a database table as integer?

string into integer

If you want to string datatype into integer using SQL Server. You can CAST an nvarchar datatype from a table (any other datatype too) into integer by first using the ROUND function as long as the data is a number. You can also CAST an expression within codes in SQL Server into integer as long as they are numeric values or numbers.


Example:

1. First lets create a table or simply decleare it as

CREATE TABLE T1(COLUMN_1 NVARCHAR(20), COLUMN_2 NVARCHAR (20))
INSERT INTO T1 VALUES(123.45,325.87)

Now we have the table T1 as:

COLUMN_1    COLUMN_2
123.45               325.87

2. USE THE QUERY BELOW

CORRECT:

SELECT ROUND(COLUMN_1,1) FROM T1
This will give 123.5

SELECT CAST(ROUND(COLUMN_1,2) AS INT) FROM T1
This will give 123

 ERROR

SELECT CAST(COLUMN_1 AS INT) FROM T1
This will give data conversion error as

"Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '123.45' to data type int."

LIKEWISE, using only a select statement, you can test the above example as:

CORRECT QUERY:

SELECT ROUND(123.49546,1)
This will give 123.5

SELECT CAST(ROUND(123.98635472,2) AS INT)
This will give 123