When you enter your query in the SQL Editor or create a calculation, you use Presto SQL syntax. To adjust text string values, you may find these common functions useful.
CONCAT
To append multiple fields' text string values together, use the CONCAT function as CONCAT(string, string). For example, if the first field contains lorem and the second contains ipsum, the calculation CONCAT({1},', ',{2}) returns lorem, ipsum.
LENGTH
To return the number of characters in a string, use the LENGTH function as LENGTH(string). For example, the query SELECT LENGTH('Workiva') returns 8.
LOWER or UPPER
To change the casing of a string, use the LOWER or UPPER function:
- To convert the string to all lowercase, use the
LOWERfunction as LOWER(string). For example, the querySELECT LOWER('Workiva')returns workiva. - To convert the string to uppercase, use the
UPPERfunction as UPPER(string). For example, the querySELECT UPPER('Workiva')returns WORKIVA.
REPLACE
To replace instances of a string within a text value with another string, use the REPLACE function as REPLACE(string, search, replace), where:
- string is the text value in which to replace text
- search is the string to replace with new text
- replace is the new string to replace the search string with
For example, the query SELECT REPLACE('lorem ipsum','ip','lo') returns lorem losum.
REVERSE
To return a string with its characters in reverse order, use the REVERSE function as REVERSE(string). For example, the query SELECT REVERSE('Workiva') returns avikroW.
SUBSTR
To return the remainder of a string from a starting position, use the SUBSTR function as SUBSTR(string, start). For example, the query SELECT SUBSTR('Workiva', 4) returns kiva.
SS_LEFT or SS_RIGHT
To return a specific number of characters from a string, use the SS_LEFT or SS_RIGHT functions.
- To start at the left of the string, use
SS_LEFTas SS_LEFT(string_value, length_value). For example,SS_LEFT('Workiva', 3)returns Wor. - To start at the right of the string, use
SS_RIGHTas SS_RIGHT(string_value, length_value). For example,SS_RIGHT('Workiva', 4)returns kiva.
TRIM, LTRIM, or RTRIM
To remove the leading and trailing spaces from a text string, use the TRIM function as TRIM(string). For example, the calculation TRIM({1}) removes any spaces before or after the text in the first field's value.
To remove only the leading or trailing spaces, use theLTRIM or RTRIM function respectively, as LTRIM(string) or RTRIM(string).
SPLIT_PART
To split a string at a specified delimiter and return only the requested field, use the SPLIT_PART function.
The requested field:
- Must be 1 or greater
- Will return null if the number entered is greater than the number of fields
For example:
SELECT SPLIT_PART('Region, Country, State', ', ', 1)returns RegionSELECT SPLIT_PART('Region, Country, State', ', ', 2)returns CountrySELECT SPLIT_PART('Region, Country, State', ', ', 3)returns State