Commonly used Cloudera Impala String Functions and Examples

  • Post author:
  • Post last modified:February 26, 2018
  • Post category:BigData
  • Reading time:6 mins read

In this article, we will discuss on the various Cloudera Impala string functions and usage. The Impala SQL string functions are similar to the SQL string functions.

Cloudera Impala String Functions

Cloudera Impala String Functions

The commonly used string functions in Cloudera Impala are listed below:

Impala String Functions Descriptions
ascii(string str) Returns the numeric ASCII code of the first character of the argument.
btrim(string a)

btrim(string a, string chars_to_trim)

 Removes all instances of one or more characters from the start and end of a STRING value. Optionally, you can provide characters to be trimmed.
length(string a)

char_length(string a)

character_length(string a)

 Returns number of characters in given string.
find_in_set(string str, string strList) Returns the position (starting from 1) of the first occurrence of a specified string within a comma-separated string.
instr(string str, string substr)

locate(string substr, string str[, int pos])

 Returns the position (starting from 1) of the first occurrence of a substring.
chr(int character_code) Returns a character specified by a decimal code point value.
initcap(string str) Returns the input string with the first letter capitalized.
group_concat(string s [, string sep]) Returns a single string representing the argument value concatenated together for each row of the result set. If the optional separator string is specified, the separator is added between each pair of concatenated values.
concat_ws(string sep, string a, string b…) Returns the concatenation of two or more string values delimited by specified separator character.
concat(string a, string b…) Returns the concatenation of two or more string values. This function provides the same functionality as the SQL-standard concatenation operator (||).
lower(string a)

lcase(string a)

 Convert string to lower case.
upper(string a)

ucase(string a)

 Convert string to UPPER case.
lpad(string str, int len, string pad) Pads the left side of string with characters to create a new string.
rpad(string str, int len, string pad) Pads the right side of string with characters to create a new string.
trim(string a) Returns the input string with both leading and trailing spaces removed.
ltrim(string a) Removes leading whitespaces from string.
rtrim(string a) Removes trailing whitespaces from string.
repeat(string str, int n) Returns the argument string repeated a specified number of times.
reverse(string a) Return string characters in reverse order.
split_part(string source, string delimiter, bigint n) Returns the nth field within a delimited string.
strleft(string a, int num_chars) Returns the leftmost specified characters of the string.
strright(string a, int num_chars) Returns the rightmost specified characters of the string.
substr(string a, int start [, int len])

substring(string a, int start [, int len])

 Returns the portion of the string starting at a specified point, optionally with a specified maximum length. The characters in the string are indexed starting at 1.
translate(string input, string from, string to) Replaces any character in string that matches a character in the from set with the corresponding character in the to set.
   

Cloudera Impala String Functions Examples

Below are some of examples on usage of Impala string functions.

pad zero using Impala String function:

Query: select lpad('12345',8,'0')
+-----------------------+
| lpad('12345', 8, '0') |
+-----------------------+
| 00012345 |
+-----------------------+
Fetched 1 row(s) in 0.81s

Query: select rpad('12345',8,'0')
+-----------------------+
| rpad('12345', 8, '0') |
+-----------------------+
| 12345000 |
+-----------------------+
Fetched 1 row(s) in 0.11s

Impala Sub-string function Examples:

Query: select substr('9875789464361',1,5)
+-------------------------------+
| substr('9875789464361', 1, 5) |
+-------------------------------+
| 98757 |
+-------------------------------+
Fetched 1 row(s) in 0.11s

Query: select substring('9875789464361',1,5)
+----------------------------------+
| substring('9875789464361', 1, 5) |
+----------------------------------+
| 98757 |
+----------------------------------+
Fetched 1 row(s) in 0.12s

Impala Translate function Examples:

Query: select translate ('111CDR222','CDR','000')
+--------------------------------------+
| translate('111cdr222', 'cdr', '000') |
+--------------------------------------+
| 111000222 |
+--------------------------------------+

Fetched 1 row(s) in 0.12s

Read:

Tags: ,