Function: concat(‘str1’, ‘str2’)

This function concatenates two strings into one.

SELECT concat('Coola','Data')
|
-->  CoolaData

Function: left(‘str’, numeric_expr)

This function returns a substring of numeric_expr characters starting the count from the leftmost character of ‘str’. The full string will be returned if the numeric_exp is longer than ‘str’.

SELECT left('CoolaData', 4);
|
-->  Cool

Function: length(‘str’)

This function returns the length of ‘str’.

SELECT length('CoolaData');
|
-->  9

Function: lower(‘str’)

This function returns ‘str’ converted to lowercase characters.

SELECT lower('CoolaData');
|
-->  cooladata

Function: lpad(‘str1’, numeric_expr, ‘str2’)

This function returns the string str2, left-padded with the string str1 to a length of numeric_expr characters. The return value is shortened to numeric_expr characters if the returned string would be longer than numeric_expr.

SELECT lpad('Data', 9, 'Coolaborator')
|
-->  CoolaData

Function: right(‘str’, numeric_expr)

This function returns the rightmost numeric_expr characters of str. If the number is longer than the string, it will return the whole string.

SELECT right('CoolaData', 4)
|
-->  Data

Function: rpad(‘str1’, numeric_expr, ‘str2’)

This function returns the string str2, right-padded with the string str1 to a length of numeric_expr characters. The return value is shortened to numeric_expr characters if the returned string would be longer than numeric_expr.

SELECT rpad('Coola', 9, 'Datamart')
|
-->  CoolaData

Function: substr(‘str’, start_from, sub_len)

This function returns a substring of str that is up to sub_len characters long, and that begins from the point in str that is specified by start_from. Counting starts at 1, so the first character in the string is in position 1 (not zero). If start_from is 3, the substring begins with the 3rd character from the left in str. If start_from is -3, the substring begins with the 3rd character from the right in str.

SELECT substr('CoolaData', -4, 4)
|
-->  Data
SELECT substr('CoolaData', 1, 4)
|
-->  Cool

Function: upper(‘str’)

This function returns the string str in uppercase letters (only Latin characters).

SELECT upper('cooladata')
|
-->  COOLADATA

Note: All regular expression support using the re2 library, see that documentation for its regular expression syntax.

Function: regexp_match(‘str’, ‘reg_exp’)

This function returns true if ‘str’ matches the regular expression.

SELECT regexp_match ('ABCabc123xyz', '23x')
|
-->  true

Function: regexp_extract(‘str’, ‘reg_exp’)

This function returns the portion of ‘str’ that matches the capturing group within the regular expression.

Function: regexp_replace(‘orig_str’, ‘reg_exp’, ‘replace_str’)

This function returns a string where any substring of orig_str that matches reg_exp is replaced with replace_str.

SELECT regexp_replace ('Hello', 'lo', 'p')
|
-->  Help

Note: Regular Expression processing is based on http://code.google.com/p/re2/.

Print Friendly, PDF & Email