STR Function - Number to String - SQL Server to Oracle Migration

In SQL Server you can use STR function to convert a decimal number to string specifying the total number of digits as well as fractional part and its rounding. In Oracle, you can use TO_CHAR function with the corresponding format.

SQL Server:

  -- Leave 1 fractional digit and round the number
  SELECT STR(123.45, 6, 1); 
  # 123.5
 
  -- Truncate the fractional part and round the number
  SELECT STR(123.7, 6);
  # 124

Oracle:

  -- Leave 1 fractional digit and round the number
  SELECT TO_CHAR(123.45, 'FM99999.9') FROM dual; 
  # 123.5
 
  -- Truncate the fractional part and round the number
  SELECT TO_CHAR(123.7, 'FM999999') FROM dual;
  # 124

For more information, see SQL Server to Oracle Migration.