How to convert int to string and vice versa in SQL?

You may use SQL CAST and CONVERT functions for converting int to string and vice versa. Both these functions are little different to use. For example:

The string to int conversion can be useful where you are taking user input and want to convert that into column’s data type before using the INSERT or UPDATE query, apart from many other situations. See the examples below for learning how to convert by using CAST and CONVERT functions.

The example of string to int by SQL CAST

The CAST function in SQL can be used as follows:

CAST ( expression AS data_type [ ( length ) ] )

Where the expression can be a text/string value, a number etc. that you want to convert into another data type.

This is followed by using the “AS” keyword.

The data_type specifies which type you want to convert that expression. There, you may specify int, bigint, smallint, tinyint, decimal, numeric, datetime, char, varchar, text etc.

The query below shows simply converting a string into int type value.

Query:

The demo of inserting a record and string to int conversion

We have a demo table, sto_employees for explaining how int to string and vice versa conversion occurs. The table stores information about employees that include name, age, salary etc.

The age is an int type column that can store only numbers. In the INSERT query, I will enter a number as string i.e. ‘35’ and used the CAST function for the string to int conversion as follows:

Query without CAST function:

String to int by CONVERT function example

This query shows inserting a record by using CONVERT function instead of CAST for the same table:

The query:

The resultset:

The example of int to string conversion

Similarly, if you have to convert int or numeric values to string, you may use the CAST and CONVERT functions for that.

See the examples below; first simple queries without the table and then inserting the records.

Int to string conversion by CAST example

The CAST query for conversion:

The example of using CAST with the INSERT statement

In this example, I will enter a record in the sto_employee table. The CAST function is used with CONCAT function for converting the int type to varchar and CONCAT is used to combine various parts of the phone number as follows:

The Query:

The phone should be stored in the table in this format:

321-456-123456

The int to string by CONVERT function example

The examples below shows converting the int to string by using CONVERT function.

A simple query:

For inserting a record example:

This should enter the phone number in the table in this format:

300-430-987458