MySQL Data Conversion Functions

Data conversion is one of the basic tasks in coding or programming. You may need to convert decimal numbers to binary, octal or hexadecimal numbers, convert uppercase characters to lowercase or vice versa. MySQL Data Conversion functions of such kind of needs are available. Here are a few commonly used MySQL Data Conversion functions which provide flexibility to programmer in doing a variety of tasks.

The general syntax of these functions is

FunctionName(argument). Argument is numeric or character depending upon the function’s working.

MySQL Data Conversion Functions

ASCII()               

ASCII function requires a character value as argument and returns its corresponding numeric ASCII value. If a string is given as an argument then this function returns the numeric (ASCII) value of the first character from left in the string. Rest of the characters of the string will be ignored by the function.

SELECT ASCII('A') FROM DUAL;
SELECT ASCII('Benny') FROM DUAL;
MySQL Data Conversion Function-ASCII

BIN()   

This function is used to get the binary representation of the argument. Only positive integer can be passed as an argument. Output will be a string of binary digits representing the number passed as argument.

SELECT BIN(12) FROM DUAL;
MySQL Data Conversion Function-BIN

CHAR(n1,n2,n3… using Character Set)       

This function is used to get the character representation for the integer arguments using the character set given at the end of the function before right parenthesis. The integer arguments are the ASCII values. Multiple numeric values separated by commas can be passed to the function. The function will return string of character values of these.

SELECT CHAR(78,77,67 using utf8);
Char Function

HEX() 

This function is used to get the hexadecimal representation of the argument passed as number. The output will be a string of hexadecimal digits representing the argument number.

SELECT HEX(20);
SELECT HEX(28);
HEX
HEX example

LCASE() / LOWER()

LOWER or LCASE functions are used to convert the string argument into equivalent lowercase string. All the uppercase characters are converted to lowercase.

SELECT LCASE('MYSQL'),LOWER('MYSQL');
LCASE LOWER

UCASE() /UPPER()

UPPER or UCASE functions are used to convert the string argument into equivalent uppercase string. All the lowercase characters are converted to uppercase.

SELECT UCASE('mysql'),UPPER('mysql');
UPPER

OCT() 

This function is used to get the octal representation of the argument passed as number. The output will be a string of octal digits representing the argument number.

SELECT OCT(17);
OCT