Change datatype of column in SQL
SQL being a dynamically manipulating database query language lets you play with your data-set that may be organized or unorganized. Such data may be presented in the form of different types depending upon your requirements. There are various methods to change the types of data present in the rows or columns of your database. Here, we will discuss the method to change the datatype of column in SQL.
Using SQL server
- Open the SQL server. In the Object Explorer option, right-click the column you want to change and click on Design.
- You need to select the column whose data type you want to modify.
- In the Column Properties, you need to click the grid cell to change the Data Type property and then choose the data type from the appeared drop-down list.
- Now, click Savetable on the File menu to save the changes.
Using ALTER TABLE
The ALTER TABLE command in SQL lets you delete, add or modify columns present in your database table. It is also used for other purposes like adding or dropping constraints on your existing database table. Create the sample database shown in the below examples. Proceed with the below steps to understand how the data type is changed.
Syntax:
ADD column_name datatype;
Example:
ADD name varchar(100);
To modify the datatype of the column:
Syntax:
ADD column_name datatype;
Example:
ADD employee_name string;
Also, using the ALTER COLUMN option in SQL, you can easily modify the data type of the given column as shown. The below query changes the datatype of the column named DateofBirth to the type year.
ALTER COLUMN DateofBirth year;
The main purpose of the alter command is not just to delete or add the columns present in your database but to modify and change it too. In the above examples, you have seen the simple and easy syntax of ALTER TABLE command in SQL. There might also arise a situation when you want to modify multiple columns in the database. To do that, you simply need to assign the column's name along with the datatype conversion you want in your newly modified column. Consider the below example.
ADD (column_1 column_definition,
column_2 column_definition,
...
column_n column_definition);
Using other databases
For Oracle, MySQL, MariaDB:
MODIFY column_name column_type;
For POSTgreSQL:
ALTER COLUMN column_name TYPE column_definition;
Also, if you do not want to lose data while changing the datatype of the respective column, you might see the below example for reference.
(
ID int primary key ID,
Name varchar(50),
Sex varchar(50),
Incentives nvarchar(50)
)
To know what datatype your column is, you need to type the below command which tells you the data type of the column you want to change.
Syntax:
WHERE Table.schema = "Your_database_name"
AND table_name = "Your_table_name"
To understand this is quite a depth, let's create a database to observe how datatypes of columns can be brought out.
create table DataTypeDemo
(
Id int,
Venue varchar(100),
Amount decimal(9,3)
);
Query:
WHERE table_schema = "Company"
AND table_name = "Attendance"
In the above example, the output of the query will roll out the datatype of the respective columns. We used MySQL since the syntax is quite familiar and easy to understand.
Summary
In this article, you learned how you can easily change the data types of your desired columns in SQL, MySQL, or any other databases you might be using. There are no such hard and fast rules to write the queries in capital or small letter provided some data types are case-sensitive and should be used only with prior knowledge. If you're working with huge amounts of data, rolling out all the data types back to previous data types is not an easy task; rather you would find it more difficult to arrange them after converting. Thus, one should carefully figure out the fragile measures before opting to change the data types of the columns in your desired database table.
