Convert types USING a function

If you don't want to reserve too much space for a certain varchar column, you can truncate the values before converting its type.

For this, you can use the following syntax:

ALTER TABLE table_name
ALTER COLUMN column_name
TYPE varchar(x)
USING SUBSTRING(column_name FROM 1 FOR x)

You should read it like this: Because you want to reserve only x characters for column_name, you have to retain a SUBSTRING of every value, i.e. the first x characters of it, and throw away the rest. This way, the values will fit the varchar(x) requirement.

This exercise is part of the course

Introduction to Relational Databases in SQL

View Course

Exercise instructions

Run the sample code as is and take note of the error.
Now use SUBSTRING() to reduce firstname to 16 characters so its type can be altered to varchar(16).

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Convert the values in firstname to a max. of 16 characters
ALTER TABLE professors 
ALTER COLUMN firstname 
TYPE varchar(16)

Edit and Run Code