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

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

In PostgreSQL, you can use the TO_CHAR function with the corresponding format.

SQL Server:

  -- Leave 1 fractional digit and round the number
  SELECT STR(123.45, 5, 1); 
  /* 123.5 */
 
  -- Truncate the fractional part and round the number
  SELECT STR(123.7, 3);
  /* 124 */
 
  -- Shorter string is left-padded with blanks
  SELECT STR(1, 3)
  /*    1 */

PostgreSQL:

  -- Leave 1 fractional digit and round the number
  SELECT TO_CHAR(123.45, 'FM99999.9'); 
  /* 123.5 */
 
  -- Truncate the fractional part and round the number
  SELECT TO_CHAR(123.7, 'FM999999');
  /* 124 */
 
  -- Using LPAD to left-pad short strings with blanks
  SELECT LPAD(TO_CHAR(1, 'FM999'), 3);
  /*    1 */

The STR function left-pads shorter strings with blanks to the specified number of characters:

SQL Server:

  -- Demonstrate padding by replacing blanks with #
  SELECT REPLACE(STR(1, 3), ' ', '#');
  /* ##1 */
 
  -- Pads to 10 characters by default
  SELECT REPLACE(STR(1), ' ', '#');
  /* #########1 */

The reason we use the 'FM999' format in PostgreSQL, which suppresses padding blanks, and then use the LPAD function is that the '999' format adds one extra blank for the sign.

PostgreSQL:

  -- '999' pads to 4 characters including the sign
  SELECT REPLACE(TO_CHAR(1, '999'), ' ', '#');
  /* ###1 */
 
  -- LPAD helps pad to 3 characters  
  SELECT REPLACE(LPAD(TO_CHAR(1, 'FM999'), 3), ' ', '#');
  /* ##1 */

For more information, see SQL Server to PostgreSQL Migration.