SQL Query to Convert VARCHAR to INT

In SQL, converting data types is a common operation, and one of the most frequently required conversions is from VARCHAR to INT. This conversion is necessary when we need to perform mathematical operations or comparisons on numeric values stored as strings. SQL Server provides several methods to achieve this, including CAST(), CONVERT(), and error-handling functions like TRY_CAST() and TRY_CONVERT().

In this article explains how to efficiently convert VARCHAR to INT in SQL, with practical examples and detailed syntax.

SQL Methods to Convert VARCHAR to INT

1. CAST() Function

The CAST() function in SQL Server is used to explicitly convert an expression from one data type to another. It's one of the simplest and most straightforward ways to convert a VARCHAR value into an INT. CAST() is ideal when we are sure that the VARCHAR value contains only numeric characters and that the conversion will succeed.

Syntax :

CAST ( expression AS target_type [ ( length ) ] )

Key Terms

• expression – Any value of any type that will be converted.
  
• target_type – Target data type to which the value will be converted. e.g. INT, BIT, SQL_VARIANT, etc.
  
• length – Optional parameter that specifies the length of the target_type, default length is 30.

Example

If we have a column storing numeric data as strings and you want to convert the value '1234' to an INT, use the following query:

SELECT CAST('1234' AS INT) AS Result;

Output:

2. Convert() Function

Similar to the CAST() function, the CONVERT() function in SQL Server is used to change a value from one data type to another. CONVERT() is more versatile because it allows us to specify additional formatting options such as length and style, which is especially useful when dealing with dates or numeric formatting.

Syntax :

SELECT CONVERT ( target_type ( length ), expression )

Example

To convert the string '5678' into an INT, use the following query:

SELECT CONVERT(INT,'5678') AS Result;

Output:

Error-Handling Methods: TRY_CAST() and TRY_CONVERT()

When working with VARCHAR values that might not always be convertible to INT (for example, strings with non-numeric characters), SQL Server provides TRY_CAST() and TRY_CONVERT(). These functions help avoid errors by returning NULL when a conversion fails.

3. TRY_CAST() Function

The TRY_CAST() function attempts to cast the input value to a value of the given data type. If the cast is successful, it returns the value in the provided data; else, it returns NULL. However, if we request a conversion that is not valid, the TRY_CAST() method will return an error.

Syntax :

TRY_CAST ( expression AS data_type [ ( length ) ] )

Example

SELECT TRY_CAST('1234' as INT) as Result;

Output

If the conversion fails:

SELECT TRY_CAST('1234abc' as INT) as Result;

Output:

4. TRY_CONVERT() Function

The TRY_CONVERT() method attempts to convert the value supplied to it to the data type specified. If the cast is successful, it returns the value as the given data; else, it returns NULL. If you request a conversion that is explicitly forbidden, the TRY CONVERT() method will return an error.

Syntax :

TRY_CONVERT ( data_type[(length)], expression [,style])

Example

SELECT TRY_CONVERT( INT ,'5678') as Result;

Output

If the conversion fails:

SELECT TRY_CONVERT( INT ,'56abc') as Result;

Output

Conclusion

Converting VARCHAR to INT in SQL is a straightforward task using the CAST() and CONVERT() functions. These methods work well for valid numeric strings, but for more robust error handling, TRY_CAST() and TRY_CONVERT() provide a safer way to perform conversions without causing errors. By understanding these functions and their use cases, we can confidently work with data that requires conversion between different data types.

sanuj8655

Follow

Improve

Article Tags :

• SQL
• Databases
• SQL-Server
• SQL-Query