CONVERT Function - To Number - SQL Server to Oracle Migration

In SQL Server, you can use the CONVERT function to convert an expression to a numeric type.

In Oracle, you can use the CAST function but data types can be different.

SQL Server:

  -- Convert string to number
  SELECT CONVERT(DECIMAL(5,2), '123.45'); 
  /* 123.45 */

Oracle:

  -- Convert string to number
  SELECT CAST('123.45' AS NUMBER(5,2)) FROM dual; 
  /* 123.45 */

Rounding Decimals

Note that both the SQL Server CONVERT and the 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 CONVERT(DECIMAL(5,1), '123.45'); 
  /* 123.5 */
 
  -- Rounding down
  SELECT CONVERT(DECIMAL(5,1), '123.43'); 
  /* 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.