Apache Drill SQL String Manipulation Function

Apache Drill provides supports for the string functions which are used to perform operations such as concatenate two strings, get the length of the string, add padding to the string from left and from right, find the position of the character, convert string in upper, lower and so on.

We can use the below list of Apache Drill String functions in the Drill queries.

Drill Date/Time Functions Return Type
BYTE_SUBSTR BINARY or VARCHAR
CHAR_LENGTH INTEGER
CONCAT VARCHAR
ILIKE BOOLEAN
INITCAP VARCHAR
LENGTH INTEGER
LIKE BOOLEAN
LOWER VARCHAR
LPAD VARCHAR
LTRIM VARCHAR
POSITION INTEGER
REGEXP_MATCHES BOOLEAN
REGEXP_REPLACE VARCHAR
REPEAT VARCHAR
REPLACE VARCHAR
REVERSE VARCHAR
RPAD VARCHAR
RTRIM VARCHAR
SPLIT INTEGER
SPLIT_PART VARCHAR
STRPOS INTEGER
SUBSTR VARCHAR
TOASCII VARCHAR
TRIM VARCHAR
UPPER VARCHAR

Let us see some examples of Apache Drill String functions.


1. Apache Drill CHAR_LENGTH Function

CHAR_LENGTH evaluates the input string and return the number of character present in the expression.

CHAR_LENGTH Function Syntax:

The following is the syntax of the CHAR_LENGTH Function.

CHAR_LENGTH(string)

CHAR_LENGTH Examples

In the following example, we are fetching the length of "CloudDuggu".

Command:

apache drill> SELECT CHAR_LENGTH('CloudDuggu') FROM (VALUES(1));

Output:

drill date add function cloudduggu


2. Apache Drill CONCAT Function

CONCAT function is used to concatenate two arguments and return the result.

CONCAT Function Syntax:

The following is the syntax of the CONCAT Function.

CONCAT(string [, string [, ...] )

CONCAT Examples

In the following example, we will concatenate the Cloudduggu and Tutorials together.

Command:

apache drill> SELECT CONCAT('Cloudduggu', ' ', 'Tutorials') FROM (VALUES(1));

Output:

drill concat function cloudduggu