MySQL provides the FORMAT function to convert numbers into a comma separated format and to round to a particular decimal format. For example, we can format 4500.56 to 4,500.6. n this article, we will learn how to use format with MySQL
The basic syntax of a FORMAT is as follows:
SELECT FORMAT(number, decimal_places);
We will be using docker in this article, but feel free to install your database locally instead. Once you have docker installed, create a new file called
docker-compose.yml and add the following.
version: '3' services: db: image: mysql:latest container_name: db environment: MYSQL_ROOT_PASSWORD: root_pass MYSQL_DATABASE: app_db MYSQL_USER: db_user MYSQL_PASSWORD: db_user_pass ports: - "6033:3306" volumes: - dbdata:/var/lib/mysql phpmyadmin: image: phpmyadmin/phpmyadmin container_name: pma links: - db environment: PMA_HOST: db PMA_PORT: 3306 PMA_ARBITRARY: 1 restart: always ports: - 8081:80 volumes: dbdata:
Now, navigate to
http://localhost:8081/ to access phpMyAdmin. Then log in with the username
root and pass
Click the SQL tab and you are ready to go.
The FORMAT function is straight forward to use. We simply call the function in a select statement and pass a number we want to format with the decimal places we want to round to. Here are a few examples.
SELECT format(4500.56, 1);
SELECT format(5000000.5643434, 3);