Working with Format in MySQL

Intro

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 Syntax

The basic syntax of a FORMAT is as follows:

SELECT FORMAT(number, decimal_places);

Getting Setup

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:

Next, run docker-compose up.

Now, navigate to http://localhost:8081/ to access phpMyAdmin. Then log in with the username root and pass root_pass.

Click the SQL tab and you are ready to go.

The Format function

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);