String Functions

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

Arithmetic Functions

Function: abs(numeric_expr)

This function returns the absolute value of the numeric expression.

SELECT abs(5 - 7), abs(-2), abs(2)
|
-->  2, 2, 2

Function: ceil(numeric_expr)

This function returns the numeric expression rounded up to nearest whole number.

SELECT ceil(13 / 2), ceil(6.5)
|
--> 7, 7

Function: floor(numeric_expr)

This function returns the numeric expression rounded down to the nearest whole number.

SELECT floor(13 / 2), floor(6.5)
|
--> 6, 6

Function: pow(numeric_expr1, numeric_expr2)

This function raises the first numeric expression to the power of the second numeric expression and returns the result.

SELECT pow(3 , 2), pow(3 , -2)
|
--> 9, 0.11111111111

Function: round(numeric_expr)

This function returns the numeric expression rounded up or down to the nearest whole number.

SELECT round(6.5), round(6.49), round(6.51)
|
--> 7, 6, 7

Function: sqrt(numeric_expr)

This function returns the square root of the numeric expression.

SELECT sqrt(36)
|
--> 6
Print Friendly, PDF & Email

Arithmetic Operators

Operator: numeric_expr + numeric_expr

This operator performs the addition operation and returns the sum of the two numeric expressions.

SELECT (12+5)

-->  17

Operator: numeric_expr – numeric_expr

This operator performs the subtraction operation and returns the difference of the two numeric expressions. This operator can also be used to turn a given value into a negative value.

SELECT (17 - 5)

-->  12

Operator: numeric_expr * numeric_expr

This operator performs the multiplication of two expressions and returns the product.

SELECT (2*6)

-->  12

Operator: numeric_expr / numeric_expr

This operator performs the division of the first expression by the second expression and returns the quotient.

SELECT (12 / 6)

-->  2

Operator: numeric_expr % numeric_expr

This is the modulo operator. It returns the value of the remainder of a division operation.

SELECT (13 % 6)

--> 1
Print Friendly, PDF & Email

Aggregation Functions

An aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria, to form a single value of more significant meaning or measurement.

Function: avg(numeric_expr)

This function returns the average of the values for a group of rows computed by numeric_exp. NULL value rows are not included in the calculation.

SELECT customer_user_id, avg(amount_spent) as avg_spent
FROM cooladata
WHERE date_range(all)
GROUP BY customer_user_id

Function: count(*)

This function returns the total number of values (including NULLs) in the scope of the function.

SELECT count(*)
FROM cooladata
WHERE date_range(all)

Function: count([DISTINCT field [, exact])

This function returns the total number of non-NULL values in the scope of the function. If you use the DISTINCT keyword, the function returns the number of unique values in the specified field. If the “exact” argument is requested, exact computation will occur and no estimation will take place.

SELECT count(distinct user_id)
FROM cooladata
WHERE date_range(all)

Function: stddev(numeric_expr)

This function returns the standard deviation of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.

SELECT stddev(amont_spent) as stddev
FROM cooladata
WHERE date_range(all)

Function: variance(numeric_expr)

This function returns the variance of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.

SELECT variance(amount_spent) as spent_variance
FROM cooladata
WHERE date_range(all)

Function: max(field)

This function returns the maximum value in the scope of the function.

SELECT customer_user_id, max(amount_spent) as max_spent
FROM cooladata
WHERE year(event_time) = 2013
GROUP BY customer_user_id
ORDER BY spent DESC
LIMIT 100

Function: min(field)

This function returns the minimum value in the scope of the function.

SELECT customer_user_id, datediff(max(event_time_ts),min(event_time_ts)) as engagement_duration
FROM cooladata
WHERE date_range(all)
GROUP BY customer_user_id
ORDER BY engagement_duration
LIMIT 100

Function: sum(field)

This function returns the sum total of the values in the scope of the function. Only numeric data typed fields may be used with this function.

SELECT count(session_id) AS session_count, sum(amount_spent) as revenue
FROM cooladata
WHERE date_range(all)
Print Friendly, PDF & Email

Comparison Functions

Comparison functions return TRUE or FALSE.

Comparison functions are used as arguments with either numeric or string expressions. String constants must be enclosed in single or double quotes. The expressions can be literal or dynamically fetched by a query. Comparison functions can be used in most query clauses, but are most often used as filtering conditions in WHERE clauses.

Function: expr1 = expr2

This function checks for equality, it returns TRUE if the two expressions are equal, and FALSE if not.

SELECT 1=2, 'q'='q'
|
-->  false, true

Function: expr1 != expr2 | expr1 <> expr2

These functions check for inequality. They return TRUE if the two expressions are not equal, and FALSE if they are.

SELECT 1!=2, 'q'<>'q'
|
-->  true, false

Function: expr1 > expr2

This function checks that the expression on the left is greater than the one on the right. The function returns TRUE if the left side is greater than the right side, and FALSE if not.

SELECT 1 > 2, 2 > 1
|
-->  false, true

Function: expr1 < expr2

This function checks that the expression on the left is less than the expression on the right. The function returns TRUE if the left side is less than the right side, and FALSE if not.

SELECT 1 < 2, 2 < 1
|
-->  true, false

Function: expr1 >= expr2

This function checks that the expression on the left is greater than or equal to the one on the right. The function returns TRUE if the left side is greater than or equal to the right side, and FALSE if not.

SELECT 1 >= 1, 1 >= 2, 1 >= 0
|
-->  true, false, true

Function: expr1 <= expr2

This function checks that the expression on the left is less than or equal to the one on the right. The function returns TRUE if the left side is less than or equal to the right side, and FALSE if not.

SELECT 1 <= 1, 1 <= 2, 1 <= 0
|
-->  true, true, false

Function: expr IS NULL

This function checks if the expression evaluates to NULL. It returns TRUE if NULL, and FALSE if not.

SELECT 1 is NULL, 1 is not NULL
|
-->  false, true

Function: expr IN(expr1, expr2, …)

This function checks if the expression value is found in a list of values. If it is found, it returns TRUE, otherwise it returns FALSE. The list must contain constants or literals. In order to evaluate correctly, it must be the same data type as the expression.

SELECT 'adam' in ('ruth', 'bill', 'adam'), 'bob' in ('ruth', 'bill', 'adam'), 1 in (1,2,3,4,5,6,7,8,9)
|
-->  true, false, true

 

Function: if(condition, true_return, false_return)

This function returns either val_true or val_false, depending on whether the condition is true or false. The return values can be literals or field-derived values, but they must be the same data type. Field-derived values do not need to be included in the SELECT clause.

SELECT if(user is not NULL, user, 'Unknown user')

 

Function: IFNULL(expr, null_default)

This function offers a way to handle NULLs. If the expression is not NULL it is returned, if it is NULL then the value in null_default is returned. The expression and the default value must be the same type.

SELECT ifnull( null, true), ifnull(1,999)
|
-->  true, 1
Print Friendly, PDF & Email