REVERSE

Syntax

SQL

SELECT REVERSE(<string>);

Arguments

string: any string

Return Type

String

Examples

SQL

SELECT REVERSE('See the inventory list for details' collate utf8mb4_general_ci);

+--------------------------------------------------------------------------+
| REVERSE('See the inventory list for details' collate utf8mb4_general_ci) |
+--------------------------------------------------------------------------+
| sliated rof tsil yrotnevni eht eeS                                       |
+--------------------------------------------------------------------------+

In the examples below, the following table and values were used:

SQL

CREATE TABLE inventory(item_ID VARCHAR(25), item VARCHAR(50), 
category VARCHAR(25), cases_instock NUMERIC(4));

INSERT INTO inventory VALUES('GA001','Green Apples', 'fruit', '22'),
('RB247','Bananas', 'fruit','32'),
('GP749', 'Golden Pineapple', 'fruit', '11'),
('KM991', 'Kent Mango', 'fruit', '5'),
('FP860', 'Fuyu Persimmons', 'fruit','2');

SQL

SELECT item, REVERSE(item) FROM inventory;

+------------------+------------------+
| item             | REVERSE(item)    |
+------------------+------------------+
| Bananas          | sananaB          |
| Green Apples     | selppA neerG     |
| Fuyu Persimmons  | snommisreP uyuF  |
| Kent Mango       | ognaM tneK       |
| Golden Pineapple | elppaeniP nedloG |
+------------------+------------------+

SQL

SELECT item_id, item, REVERSE(item_id), REVERSE(item) FROM inventory;

+---------+------------------+------------------+------------------+
| item_id | item             | REVERSE(item_id) | REVERSE(item)    |
+---------+------------------+------------------+------------------+
| GP749   | Golden Pineapple | 947PG            | elppaeniP nedloG |
| GA001   | Green Apples     | 100AG            | selppA neerG     |
| KM991   | Kent Mango       | 199MK            | ognaM tneK       |
| RB247   | Bananas          | 742BR            | sananaB          |
| FP860   | Fuyu Persimmons  | 068PF            | snommisreP uyuF  |
+---------+------------------+------------------+------------------+

Use REVERSE to Improve Searching

Utilizing the REVERSE function can improve the search performance when querying table data, if you add an ordered index or sort key on a persisted computed column derived from a reversed value.

Examples

Utilizing an expanded version of the inventory list above, the table and data are as follows:

SQL

CREATE ROWSTORE TABLE inventory(item_ID VARCHAR(25), item VARCHAR(50),
dept VARCHAR(25), dept_mgr_id VARCHAR(15), cases_instock NUMERIC(4));

SQL

INSERT INTO inventory VALUES('GA001','Green Apples', 'produce', 'PF9825','22'),
('RB247','Bananas', 'produce','PF9825','32'),  
('GP749', 'Golden Pineapple', 'produce','PF9825', '11'),
('KM991', 'Kent Mango', 'produce', 'PF9825','5'),
('SB984', 'Strawberries', 'produce', 'PF9825','28'),
('BB620', 'Blueberries', 'produce', 'PF9825', '15'),
('RA037', 'Red Apples', 'produce', 'PF9825','45'),
('NO900', 'Navel Oranges', 'produce', 'PF9825', '17'),
('YL100', 'Yuzu', 'produce', 'PF9825','2'),
('CAU119', 'Cauliflower', 'produce','PF9825','14'),
('ZUC329', 'Zucchini', 'produce', 'PF9825', '22'),
('EGP881', 'Eggplant', 'produce','PF9825', '11'),
('YEO992', 'Yellow onions', 'produce','PF9825','47'),
('RPO664', 'Red potatoes', 'produce', 'PF9825', '38'),
('BRO0010', 'Broccoli', 'produce','PF9825', '29')
('BSP0029', 'Baby spinach', 'produce','PF9825', '22'),;

Add a persisted column.

SQL

ALTER TABLE inventory ADD rev_item_ID AS (REVERSE(item_ID)) PERSISTED varchar(25);

Add an index to the reversed column.

SQL

CREATE INDEX rev_item_ID ON inventory(rev_item_ID);

Query the indexed, reversed column.

SQL

SELECT * FROM inventory
WHERE REVERSE(item_ID) LIKE REVERSE('%9');

+---------+-------------------+---------+-------------+---------------+-------------+
| item_ID | item              | dept    | dept_mgr_id | cases_instock | rev_item_ID |
+---------+-------------------+---------+-------------+---------------+-------------+
| ZUC329  | Zucchini          | produce | PF9825      |            22 | 923CUZ      |
| GP749   | Golden Pineapple  | produce | PF9825      |            11 | 947PG       |
| BSP0029 | Baby spinach      | produce | PF9825      |            22 | 9200PSB     |
| APF7729 | all-purpose flour | baking  | BD4259      |            47 | 9277FPA     |
| CAU119  | Cauliflower       | produce | PF9825      |            14 | 911UAC      |
+---------+-------------------+---------+-------------+---------------+-------------+

Now, observe that the plan for that query uses an index range scan on the computed column: rev_item_ID.

SQL

EXPLAIN SELECT * FROM inventory WHERE REVERSE(item_ID) LIKE REVERSE('%9');

+------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------+
| Gather partitions:all alias:remote_0 parallelism_level:partition                                                                   |
| Project [inventory.item_ID, inventory.item, inventory.dept, inventory.dept_mgr_id, inventory.cases_instock, inventory.rev_item_ID] |
| Filter [inventory.rev_item_ID LIKE REVERSE('%9')]                                                                                  |
| TableScan ticket_test.inventory table_type:sharded_rowstore                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------+

REVERSE

On this page

Syntax

Arguments

Return Type

Examples

Use REVERSE to Improve Searching

Examples

Was this article helpful?

On this page

Was this article helpful?