Formatting numbers in SQL, PHP, JS

Programming / Webdevelopment

As a web developer, some tasks keep coming up. And every time you look for the solution in the online documentation. This includes converting numbers into different formats. On this page we summarize the elementary number conversions for SQL, PHP and Javascript

How to read this page

If your have an INPUT like "1234.999" and you'd like to convert in into an OUTPUT like "1.235,00" using language SQL or PHP or Javascript your can use the code shown here as a starting point.

graph TD INPUT --> SQL --> OUTPUT INPUT --> PHP --> OUTPUT INPUT --> JS --> OUTPUT

Standard number conversion

Input

1234.999

MySQL/MariaDB

SELECT FORMAT(1234.999, 3)

PHP

$i = 1234.999;
$o = number_format($i, 3);

Javascript

var i = 1234.999;
var o = i.toLocaleString('en');

Output

1,234.999

Attention: Rounding

Input

1234.999

MySQL/MariaDB

FORMAT(1234.999, 2)

PHP

$i = 1234.999;
$o = number_format($i, 2);

Javascript

var i = 1234.999;
var options = {
  minimumFractionDigits: 2,
  maximumFractionDigits: 2
};
var o = i.toLocaleString('en', options);

Output

1,235.00

With Locales

Input

1234.999

MySQL/MariaDB

FORMAT(1234.999, 'de_DE')

PHP

$i = 1234.999;
$o = number_format($i, 2, ',', '.');

Javascript

var locale = 'de-DE';
var options = {
  minimumFractionDigits: 2,
  maximumFractionDigits: 2
};
var i = 1234.999;
var o = i.toLocaleString(locale, options);

Output

1.235,00

Do you like it?