How To Reverse String In MySQL

Many SQL queries involve string manipulation. The data residing in string data-type columns need to be massaged and transformed in a variety of ways like extracting portions of strings, hashing strings, changing the case of strings and so on. All this is achieved in MySQL through built-in string functions. In this article, we will learn how to reverse a string in MySQL.

MySQL Reverse Function – REVERSE()

The function in MySQL to reverse a string is REVERSE() . The syntax is as follows:

reverse(exp)

where exp is a valid string expression like a column, a variable or an expression involving columns and variables. 

Some examples are:

REVERSE(first_name) 

where first_name is a column of the table.

REVERSE (CONCAT(first_name, ' ', last_name)

 where first_name and last_name are columns of the table.

Therefore we get the following from REVERSE()

SELECT REVERSE('SIR')                 ---> 'RIS'

SELECT REVERSE('madam rotor')  -->  'rotor madam'

SELECT REVERSE ('palindrome')    -->  'emordnilap'

MySQL REVERSE() function is safe to be used with string data types like varchars and text and is multibyte safe ie. those encodings that utilize multiple bytes for character representations can be safely  used with REVERSE() function

CAUTION: If the argument expression to the REVERSE () function is not a string but is a number or a float then the function doesn’t fail and instead reverses the digits of the number.

Therefore:

SELECT REVERSE(134)     --> 431

SELECT REVERSE(7534.32) --> 23.4357

Reverse Function in SQL Practical Use Cases

Reverse() Function is a function which has limited practical uses. It doesn’t lend itself to many use cases. Some occasions in which it is utilized is:  

1.  Cryptography.
2.  Optimizing regular expression searches where you need to find the last occurrence of a pattern in a string.

SQL Reverse Example

Let’s create a small demonstration of SQL  Reverse function using tables.

1. CREATE a table T1.

CREATE TABLE T1 (ID INT, PRODUCT_NAME VARCHAR(100), PRICE DECIMAL(6,2));

2. INSERT sample data into the table.

INSERT INTO t1 (id, product_name, price) values (1001, 'Trustbasket', 1400.50);

INSERT INTO t1 (id, product_name, price) values (1002, 'Lakewood Croquet Set', 1233.65);

INSERT INTO t1 (id, product_name, price) values (1003, 'Desert Roll Set', 560.24);

3. Run SELECT query on T1 to view the data present in it.

SELECT * FROM T1;

Data from table T1

4. Now run the query with REVERSE function on columns.

SELECT REVERSE(PRODUCT_NAME) AS PROD_REVERSE, REVERSE(PRICE) AS PRICE_REVERSE, REVERSE(CONCAT(PRODUCT_NAME, '-' , PRICE)) CONCAT_REVERSE

FROM T1;

Output of REVERSE function on table T1