CAST is used to convert one data type to another data type. The most common data types are:

Character String Data:
     CHAR(length)
     VARCHAR(length)
     LONG VARCHAR(length)
     NATIONAL CHARACTER
     Character Large OBject (CLOB)
     NCLOB

Boolean Data

Binary Large OBject (BLOB) Data:
     GRAPHIC(length)
     VARGRAPHIC(length)
     LONG VARGRAPHIC(length)

Exact Numeric Data:
     SMALLINT
     INTEGER
     BIGINT
     DECIMAL(total,fraction) or NUMERIC

Approximate Numeric Data:
     FLOAT(precisionbits)
     REAL
     DOUBLE PRECISION

Date/Time/Timestamp Data:
     DATE
     TIME
     TIME WITH TIME ZONE
     TIMESTAMP

Intervals:
     Year-month
     Day-time

The data you are converting must be compatible with the new data type. Here are some possible conversions:

> Any character type to any other type such as date, time, or numeric.
> Any type to any character type.
> Any numeric type to any other numeric type.
> Any numeric type to an interval such as INTERVAL DAY, or INTERVAL MINUTE.
> Any DATE to a TIMESTAMP where the time portion will be zero.
> Any TIME to a TIME with fractional seconds, or a TIMESTAMP where the date is the current date.
> Any TIMESTAMP to a DATE, TIME, or TIMESTAMP with different fractional seconds.
> Any year-month INTERVAL to another year-month INTERVAL with different leading precision.
> Any day-time INTERVAL to another day-time INTERVAL with different leading precision.
> DB2, MySQL, PostgreSQL, and Sybase support CAST.

Search CASE allows you to use alternate comparison ranges to determine the results.

          SELECT STORE_NAME,
                 CASE
                      WHEN YTD_QUOTA IS NULL
                           THEN 'Unknown'
                      WHEN YTD_QUOTA < 100000
                           THEN 'less than 100,000'
                      WHEN YTD_QUOTA < 5000000
                           THEN 'between 100,000 and 5,000,000'
                      ELSE 'over 5,000,000'
                 END
                      AS 'Store Quota'
            FROM STORE_TBL
           ORDER BY YTD_QUOTA;

Result:

Simple CASE allows you to choose an alternate value instead of using the actual value.

          SELECT CITY,
                 CASE STATE
                      WHEN 'CA'
                           THEN 'CALIFORNIA'
                      WHEN 'FL'
                           THEN 'FLORIDA'
                      WHEN 'LU2'
                           THEN 'LAGRANGE POINT 2'
                      WHEN 'MS1'
                           THEN 'MARS REGION 1'
                      ELSE STATE
                 END
                      AS 'STATE'
            FROM CUSTOMER_TBL
           ORDER BY CITY;

Result: