Numeric Base Conversion in MySQL

Introduction

MySql provides various function to convert a number from one base to other base. Commonly used function for base conversion are

• BIN()
• OCT()
• HEX()
• CONV()

BIN()

This function is used for converting from decimal to binary. Syntax of the function is

BIN(number)

number is the decimal number you want to convert to binary number. It operate on both strings and integers.

SELECT BIN(1);
SELECT BIN('1');

/* Output */
1
1

OCT()

This function is used for converting from decimal to octal. Its syntax is

OCT(number)

number is the decimal number you want to convert to octal. It operate on both strings and integers.

SELECT OCT(1);
SELECT OCT('1');

/* Output */
1
1

HEX()

This function is used for converting from decimal to hexadecimal. Its syntax is

HEX(number)

This function operate on both strings and integers. It will convert integers to hexadecimal numbers. For strings it will convert each character into a two-digit hexadecimal number and string them together without delimiters.

SELECT HEX(1);
SELECT HEX('1');

/* Output */
1
31

If it is in quotes it is evaluated as a string else evaluated as a number.

CONV()

This function convert a number from one base to another. This function is general purpose for base conversion. Syntax of this function is

CONV(number, from_base, to_base)

from_base is the base of number, and to_base is the base you want to convert the number into. Below example converts decimal number to its binary representation.

SELECT CONV(15, 10, 2)

/* Output */
1111

Reference

Hexadecimal Literals