SQL text string functions

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 LOWER function as LOWER(string). For example, the query SELECT LOWER('Workiva') returns workiva.
• To convert the string to uppercase, use the UPPER function as UPPER(string). For example, the query SELECT 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_LEFT as SS_LEFT(string_value, length_value). For example, SS_LEFT('Workiva', 3) returns Wor.
• To start at the right of the string, use SS_RIGHT as 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 Region
• SELECT SPLIT_PART('Region, Country, State', ', ', 2) returns Country
• SELECT SPLIT_PART('Region, Country, State', ', ', 3) returns State