SQL CAST and CONVERT functions
You may use SQL CAST and CONVERT functions for converting int to string and vice versa.
Both these functions are a little different to use. For example:
CAST('195' AS int);
CONVERT(int, '225');
The string to int conversion can be useful where you are taking user input and want to convert that into a 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.
Syntax of SQL CAST function
The CAST function in SQL can be used as follows:
Where:
| Arguments | Description |
| expression | The expression can be a text/string value, a number, etc. that you want to convert into another data type. |
| data_type | 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. |
| length |
|
The example of string to int by SQL CAST function
The query below shows simply converting a string into int type value.
Query:
SELECT CAST('4000' AS int)
AS "String to Int";
Insert a record with 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 including 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 a string i.e. ‘35’, and use the CAST function for the string to int conversion as follows:
Query with CAST function:
INSERT INTO sto_employees
VALUES (8, 'Jay', CAST ('36' AS int) ,5500, '2017-08-23');
The result after INSERT with CAST query:
String to int by CONVERT function example
This query shows inserting a record by using the CONVERT function instead of CAST for the same table:
The query:
INSERT INTO sto_employees VALUES (9, 'Jellani', CONVERT (int, '35') ,5500, '2017-10-13');
The resultset:
An 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:
SELECT CAST(225 AS varchar(100)) AS "Int to String";
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 the 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:
INSERT INTO sto_employees VALUES (10, 'Imran', 35, 5500, '2017-10-13', CONCAT(CAST(321 AS varchar),'-',CAST(456 AS varchar),'-', CAST(123456 AS varchar)));
The phone should be stored in the table in this format:
The int to string by CONVERT function example
The examples below show converting the int to string by using the CONVERT function.
A simple query:
SELECT CONVERT(varchar, 225) AS "CONVERT - Int to String";
For inserting a record example:
INSERT INTO sto_employees VALUES (11, 'Gufran', 35, 5500, '2017-10-13', CONCAT(CONVERT(varchar, 300),'-',CONVERT(varchar, 430),'-', CONVERT(varchar, 987458)));
This should enter the phone number in the table in this format: