Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Converting from One Datatype to Another

Some SQL engines do a lot of autoconverting. They allow you to use character functions with numbers or dates and number functions with character datatype numbers. Other systems are not as forgiving. SQL provides convert functions to help when autoconverting isn't available.

For example, the percentage of royalty each author gets is listed as a decimal value. If you want to display it with a percent sign, you can multiply by 100 and convert to an integer. Our sample database uses the ANSI CAST function.

CAST (expr AS datatype)

Here is a query that shows both the original format and the CAST result:

SQL

select title_id, au_id,  royaltyshare,
     cast ( royaltyshare * 100 as int) || '%' as percent from titleauthors ...