Introduction
In many SQL databases, the LEFT() function is used to extract a specified number of characters from the left side of a string, while the RIGHT() function extracts characters from the right side. However, Firebolt does not directly support these functions. By the end of this article, you will know how to use the SUBSTR()function to achieve the same results in Firebolt.
TL;DR
-
Use SUBSTR() instead of
LEFT()to get characters from the left of a string -
Use SUBSTR() and LENGTH() as an efficient alternative to
RIGHT()
Step-by-Step Guide
All the example SQL code uses the Ultra Fast Gaming data set. To familiarize yourself with this data set, visit this link: Ultra Fast Gaming Sample Dataset.
Step 1: Implementing LEFT() using SUBSTR()
To simulate the LEFT() function, use the SUBSTR() function to extract characters from the beginning of a string.
Example SQL code:
SELECT SUBSTR(nickname, 1, 6) FROM players WHERE nickname = 'murrayrebecca';
-- returns "murray"
Step 2: Implementing RIGHT() using SUBSTR() with LENGTH() function
For an efficient alternative to the RIGHT() function, use SUBSTR() and LENGTH() to directly extract characters from the end of the string.
Note: when you want to retrieve the last X characters, you need to subtract x-1 characters from the size calculated by LENGTH(column).
Example SQL code:
SELECT SUBSTR(nickname, LENGTH(nickname)-6) FROM players WHERE nickname = 'murrayrebecca';
-- returns "rebecca"
The example extracts the last 7 characters of the string ‘murrayrebecca’.
Full Example Code
SELECT SUBSTR(nickname, 1, 6) FROM players WHERE nickname = 'murrayrebecca';
SELECT SUBSTR(nickname, LENGTH(nickname)-6) FROM players WHERE nickname = 'murrayrebecca';