Presto provides an overloaded substring function to extract characters from a string. We will use the string “Presto String Operations” to demonstrate the use of this function.
Extract last 7 characters:
presto> SELECT substring('Presto String Operations',-7) as result;
result
---------
rations
(1 row)
Query 20210706_225327_00014_rtu2h, FINISHED, 1 node
Splits: 17 total, 17 done (100.00%)
0:00 [0 rows, 0B] [0 rows/s, 0B/s]
Extract last 10 characters:
presto> SELECT substring('Presto String Operations',-10) as result;
result
------------
Operations
(1 row)
Query 20210706_225431_00015_rtu2h, FINISHED, 1 node
Splits: 17 total, 17 done (100.00%)
0:00 [0 rows, 0B] [0 rows/s, 0B/s]
Extract the middle portion of the string:
presto> SELECT substring('Presto String Operations',8,6) as result;
result
--------
String
(1 row)
Query 20210706_225649_00020_rtu2h, FINISHED, 1 node
Splits: 17 total, 17 done (100.00%)
0:01 [0 rows, 0B] [0 rows/s, 0B/s]
Extract the beginning portion of the string:
presto> SELECT substring('Presto String Operations',1,6) as result;
result
--------
Presto
(1 row)
Query 20210706_225949_00021_rtu2h, FINISHED, 1 node
Splits: 17 total, 17 done (100.00%)
0:00 [0 rows, 0B] [0 rows/s, 0B/s]