CAST Function - SQL Server to Oracle Migration

In SQL Server you can use CAST function to convert an expression to another data type. In Oracle you can also use CAST function but data types can be different.

SQL Server:

  -- Convert number to string
  SELECT CAST(123.45 AS VARCHAR(6));

Oracle:

  -- Convert number to string
  SELECT CAST(123.45 AS VARCHAR2(6)) FROM dual;

Rounding Decimals

Note that the SQL Server and Oracle CAST functions round decimal values if the precision of an input expression exceeds the target data type precision.

SQL Server:

  -- Rounding up during conversion
  SELECT CAST('123.45' AS DECIMAL(5,1))
  /* 123.5 */
 
  -- Rounding down
  SELECT CAST('123.43' AS DECIMAL(5,1)) 
  /* 123.4 */

Oracle:

  -- Rounding up during conversion
  SELECT CAST('123.45' AS NUMBER(5,1)) FROM dual; 
  /* 123.5 */
 
  -- Rounding down
  SELECT CAST('123.43' AS NUMBER(5,1)) FROM dual; 
  /* 123.4 */

For more information, see SQL Server to Oracle Migration.