REVERSE
On this page
Reverses the character order of a string or returns a NULL if the parameter is NULL.
Syntax
SELECT REVERSE(<string>);
Arguments
-
string: any string
Return Type
String
Examples
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:
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');
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 |
+------------------+------------------+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:
CREATE ROWSTORE TABLE inventory(item_ID VARCHAR(25), item VARCHAR(50),dept VARCHAR(25), dept_mgr_id VARCHAR(15), cases_instock NUMERIC(4));
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.
ALTER TABLE inventory ADD rev_item_ID AS (REVERSE(item_ID)) PERSISTED varchar(25);
Add an index to the reversed column.
CREATE INDEX rev_item_ID ON inventory(rev_item_ID);
Query the indexed, reversed column.
SELECT * FROM inventoryWHERE 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_.
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 |
+------------------------------------------------------------------------------------------------------------------------------------+Last modified: June 23, 2023