How to Convert nvarchar to int in SQL

You can convert nvarchar(string/text) into an integer using SQL commands like CAST, or CONVERT. SQL, Structured Query Language, is a tool to manage and interact with databases. SQL allows us to convert datatypes using CAST, CONVERT, TRY_CAST, and TRY_CONVERT. In this blog, we will learn more about these commands in detail.

Table of Contents:

• Various Methods to Convert nvarchar to int in SQL
  • Method 1: Using CAST to convert nvarchar to int
  • Method 2: Using CONVERT for converting nvarchar to int
  • Method 3: Handling non-compatible data with TRY_CAST and TRY_CONVERT
    • Practical Example to convert nvarchar to int in SQL
  • Method 4: Data Conversion with ISNUMERIC and CAST
• Conclusion
• FAQs

Various Methods to Convert nvarchar to int in SQL

Method 1: Using CAST to convert nvarchar to int

The CAST method takes two parameters, one is the value you want to convert (nvarchar) and the other is the datatype you want to convert it to(int). This function is used to convert one datatype into another in SQL. If nvarchar contains non-numeric data then it cannot be converted to an integer, and it throws an error.

Syntax

SELECT CAST(your_column AS INT) AS ConvertedValue 
FROM Table_Name;;

Example:

Let’s assume you have a table Intellipaat_Table with the following data in the column user_id which is of nvarchar type:

Run Query:

SELECT CAST(user_id AS INT) AS ConvertedValue

FROM Intellipaat_Table;

Expected Output:

Method 2: Using CONVERT for converting nvarchar to int

It is an additional datatype conversion method that is more flexible than CAST since it allows some style modifications. This is used in databases like: Sybase and Microsoft SQL. The CONVERT method takes three arguments: the target data type, the source data, and an optional style parameter for formatting.

Syntax

SELECT CONVERT(INT, your_column) AS ConvertedValue 
FROM Table_Name;

Example:

Let’s assume you have a table Intellipaat_Table with the following data in the column user_id which is of nvarchar type:

Run Query:

SELECT CONVERT(INT, your_column) AS ConvertedValue

FROM Intellipaat_Table;

Expected Output:

Method 3: Handling non-compatible data with TRY_CAST and TRY_CONVERT

CAST and CONVERT throw errors when non-compatible data like non-numeric data is required to be converted. To avoid this error, you can use TRY_CAST or  TRY_CONVERT functions, as it will return NULL instead of throwing errors.

1. TRY_CAST

Syntax

SELECT TRY_CAST(column_name AS INT) AS ConvertedValue

FROM Intellipaat_Table;

2. TRY_CONVERT

Syntax:

SELECT TRY_CONVERT(INT, column_name) AS ConvertedValue FROM Intellipaat_Table;

Practical Example to convert nvarchar to int in SQL

Let’s understand more with the example. Suppose we have a table of students, with basic information like student_id and name.

Step 1: Create the table

CREATE TABLE Intellipaat_Students (

    student_id NVARCHAR(10),

    name VARCHAR(50)

);

Step 2: Update the sample data

INSERT INTO Intellipaat_Students (student_id, name)

VALUES

    ('101', 'Alice'),

    ('102', 'Bob'),

    ('103', 'Charlie'),

    ('ABC', 'David');

Table : students

Step 3: Convert student_id from NVARCHAR to INT Using TRY_CAST

This function will try to convert the value and return NULL if the conversion fails.

SELECT

    student_id,

    TRY_CAST(student_id AS INT) AS student_id_int,

    name

FROM

    Intellipaat_Students;

Expected Output:

Here, the student_id values ‘101’, ‘102’, and ‘103’ are successfully converted to integers. Whereas, the student_id value ‘ABC’ is non-numeric, so TRY_CAST returns NULL for student_id_int.

The TRY_CONVERT function works similarly. You just need to use TRY_CONVERT in the query as explained in the TRY_CONVERT syntax.

Method 4: Data Conversion with ISNUMERIC and CAST

If you are dealing with Non-numeric characters, you can filter them out using the ISNUMERIC method. ISNUMERIC method checks whether the data is numeric or not, before attempting to convert. It filters out the rows that cannot be converted and provides you with valid, clean, and filtered data. ISNUMERIC method returns true for values like decimals, negative numbers, and even numeric-looking strings.

Syntax:

SELECT CAST(your_column AS INT) AS ConvertedValue

FROM your_table

WHERE ISNUMERIC(your_column) = 1;

EXAMPLE: Let us assume that you have table users with a column user_id containing both numeric and non-numeric values.

Table users:

Run Query:

SELECT CAST(user_id AS INT) AS ConvertedUserID

FROM users

WHERE ISNUMERIC(user_id) = 1;

Expected Output:

In the above example, the ISNUMERIC(user_id) function identifies and includes only the rows with valid numeric values (like 123, -456.78, 789.10, 1001) and the non-numeric values like ‘intellipaat’ and ‘hello123’ are excluded from the result. The CAST(user_id AS INT) will convert numeric values into integers.

Conclusion

CAST and CONVERT functions are used to convert the data into integers, where sometimes it throws an error. To handle this error, TRY_CAST or TRY_CONVERT is used to return as NULL instead of errors. ISNUMERIC is used to filter out the Non-Numeric value before conversion.

FAQs