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.

Questo esercizio fa parte del corso

Introduction to Relational Databases in SQL

Visualizza il corso

Istruzioni dell'esercizio

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).

Esercizio pratico interattivo

Prova a risolvere questo esercizio completando il codice di esempio.

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

Modifica ed esegui il codice