SQL/R A.03.00 SQL Functions
SQL Functions
SQL/R implements the standard ODBC functions.- SQL Scalar Functions return a single value,
based on the input value.
- SQL Aggregate Functions return a single value, calculated from values in a column.
SQL Scalar functions
SQL scalar functions return a single value, based on the input value. They are in the following categories:Numeric Functions
- ABS(n)
- ABS returns the absolute value of n.
The returned data type is identical to the argument type.
ABS(-5) » 5
- ACOS(f)
- ACOS returns the arc cosine of f.
The argument must be in the range of -1 to 1.
The function returns a value in the range of 0 to pi, expressed
in radians.
The returned data type is FLOAT.
ACOS(.4) » 1.159279480727410
- ASIN(f)
- ASIN returns the arc sine of f.
The argument must be in the range of -1 to 1.
The function returns a value in the range of -pi/2 to pi/2, expressed
in radians.
The returned data type is FLOAT.
ASIN(.4) » 0.411516846067488
- ATAN(f)
- ATAN returns the arc tangent of f.
The function
returns a value in the range of -pi/2 to pi/2, expressed in radians.
The returned data type is FLOAT.
ATAN(.4) » 0.380506377112365
- ATAN2(f1, f2)
- ATAN2 returns the arc tangent of f1 / f2.
The function
returns a value in the range of -pi to pi, depending on the signs
of f1 and f2, expressed in radians.
The returned data type is FLOAT.
ATAN2(.3,.2) » 0.982793723247329
- BAND(n1, n2)
- BAND returns a bitwise AND of the arguments,
both of which must resolve to integers and returns an integer.
BAND(15,4) » 4
- BITAND(n1, n2)
- BITAND() is a synonym for BAND().
Supported from version [A.03.13]
- BIT(n, bit)
- BIT returns nonzero if the specified bit is set in n.
The arguments must resolve to integers, the bit number must be between 0
and 31.
BIT(8,3) » 1
- CEILING(n)
- CEILING returns the smallest integer greater than
or equal to n. The return value is of the same data type as the input
parameter.
CEILING(1.23) » 2 CEILING(-1.23) » -1
- CEIL(n)
- CEIL() is a synonym for CEILING().
Supported from version [A.03.13]
- COS(f)
- COS returns the cosine of f (given in radians).
The returned data type is FLOAT.
COS(PI()) » -1
- COT(f)
- COT returns the cotangent of f (given in radians).
The returned data type is FLOAT.
COT(12) » -1.572673406397690
- DEGREES(n)
- DEGREES converts the argument from radians to degrees.
This function is typically used with trigonometric functions.
The returned data type is FLOAT.
DEGREES(PI()) » 180
- DIV(n1, n2)
- DIV returns result of n1 divided by n2 truncated to
an integer (same as TRUNCATE(n1/n2,0)).
DIV(129,10) » 12
- EXP(f)
- EXP returns the exponential value of e
(the base of natural logarithm) raised to the power of f.
The returned data type is FLOAT.
EXP(2) » 7.389056098930650
- FLOOR(n)
- FLOOR returns the largest integer less than or
equal to n. The return value is of the same data type as the input
parameter.
FLOOR(1.23) » 1 FLOOR(-1.23) » -2
- LOG(f)
- LOG returns the natural logarithm of f,
where f is greater than 0.
The returned data type is FLOAT.
LOG(2) » 0.69314718055995
- LOG10(f)
- LOG10 returns the base 10 logarithm of f,
where f is greater than 0.
The returned data type is FLOAT.
LOG10(100) » 2
- MOD(n1, n2)
- MOD returns the remainder (modulus) of
n1 divided by n2.
MOD(123,10) » 3
- PI()
- PI returns the value of the constant π (pi).
The returned data type is FLOAT.
PI() » 3.141592653589790
- POWER(n1, n2)
- POWER returns n1 raised to power of n2.
The returned data type is FLOAT.
POWER(2,2) » 4 POWER(2,-2) » 0.25
- RADIANS(n)
- RADIANS converts the argument from degrees to radians.
This function is typically used with trigonometric functions.
The returned data type is FLOAT.
RADIANS(90) » 1.570796326794890
- RAND([seed])
- RAND returns a random floating-point value
in the range from 0 to 1. If the optional seed argument is specified,
it is used as the seed value, which produces a repeatable sequence
of column values.
The returned data type is FLOAT.
RAND() » 0.840187717154710
- ROUND(n, places)
- ROUND returns n rounded to the specified
decimal places right of the decimal point. The argument places may
be negative to round off digits left of the decimal point.
The returned data type is FLOAT.
ROUND(15.19,1) » 15.2 ROUND(15.19,-1) » 20
- SIGN(n)
- SIGN returns the sign of n.
The function returns a value of -1 for negative values,
zero for a value of 0 and +1 for positive values.
If the expression is NULL then the return value is also NULL.
SIGN(-3) » -1
- SIN(f)
- SIN returns the sine of f (given in radians).
Returned data type is FLOAT.
SIN(.5) » 0.479425538604203
- SQRT(f)
- SQRT returns the square root of f.
Returned data type is FLOAT.
SQRT(20) » 4.472135954999580
- TAN(f)
- TAN returns the tangent of f (given in radians).
The returned data type is FLOAT.
TAN(PI()+1) » 1.557407724654910
- TRUNCATE(n, places)
- TRUNCATE returns n truncated to the specified
decimal places.
Places may be negative to truncate (make zero) digits left of the
decimal point.
The returned data type is FLOAT.
TRUNCATE(15.79,1) » 15.7 TRUNCATE(15.79,-1) » 10
String Functions
- ASCII(string)
- ASCII returns the ASCII code value of the
leftmost character of string as an integer.
ASCII('A') » 65
- CHAR(code)
- CHAR returns the character that has the ASCII code
value specified by code. The value of code should be between 0 and 255.
CHAR(65) » A
- CONCAT(string1, string2)
- CONCAT returns a character string that is the
result of concatenating string2 to string1.
CONCAT('Hello ','World!') » Hello World!
The SQLR operator '&' has the same effect. - INSERT(string1, start, length, string2)
- INSERT returns a string where length characters
have been deleted from string1, beginning at start, and replaced by
string2.
INSERT('Hello',3,2,'y') » Heyo
- LCASE(string)
- LCASE returns the string converted into lower
case letters.
LCASE('LOWER') » lower
- LEFT(string, count)
- LEFT returns the count leftmost characters.
LEFT('ABC',2) » AB
- LENGTH(string)
- LENGTH returns the number of characters,
excluding trailing blanks.
LENGTH('AB ') » 2
- LOCATE(string1, string2 [,start])
- LOCATE returns the first starting position of a
string1 within string2.
If start is specified, the search begins with the character position
indicated by the value of start.
The first character position in string2 is indicated by the value 1.
If string1 is not found within string2, the value 0 is returned.
LOCATE('b','abcba',3) » 4
- LTRIM(string)
- LTRIM returns string, with leading blank characters removed.
LTRIM(' ABC') » ABC
- REPEAT(string, count)
- REPEAT returns a character string composed of
string repeated count times.
REPEAT('*',3) » ***
- REPLACE(string1, string2, string3)
- REPLACE replaces all occurrences of string2 in
string1 with string3.
REPLACE('axcxa','x','b') » abcba
- RIGHT(string, count)
- RIGHT returns the count rightmost characters
of string.
RIGHT('abc',2) » bc
- RTRIM(string)
- RTRIM returns string, with trailing blank characters
removed.
RTRIM('ABC ') » ABC
- SPACE(count)
- SPACE returns a string of count blank characters.
This is the same as REPEAT(' ',count).
- STRINGFMT(format, ...)
- STRINGFMT returns a formatted string. The format
specifies how to format the arguments (follows printf conventions).
The number of arguments is variable.
STRINGFMT('Price is %6.2f %s',1.23,'EUR') » Price is 1.23 EUR
- SUBSTRING(string, start, length)
- SUBSTRING returns string beginning at the
character position specified by start for length characters.
SUBSTRING('ABC',2,1) » B
- UCASE(string)
- UCASE returns the string converted into upper
case letters.
UCASE('upper') » UPPER
Date and Time Functions
- CURDATE()
- CURDATE returns the current date.
- CURTIME()
- CURTIME returns the current time.
- DATE(year, month, day)
- DATE returns a date value from the given arguments.
DATEFMT("%Y-%m-%d",DATE(2014,11,21)) » 2014-11-21
- DATEFMT(format, date_time)
- DATEFMT returns a formatted date or time.
DATEFMT("%Y-%m-%d %H:%M:%S", NOW()) » 2014-11-21 17:00:39
Ordinary characters are copied verbatim. Formatting codes starting with a % sign are replaced as below.Format Description Example %a Abbreviated weekday name Sun through Sat %A Full weekday name Sunday through Saturday %b Abbreviated month name Jan through Dec %B Full month name January through December %c Date and time stamp Tue Feb 5 00:45:10 2009 %d Two-digit day of the month (with leading zero) 01 to 31 %e Day of the month (with leading space) 1 to 31 %H Two digit representation of the hour in 24-hour format 00 through 23 %I Two digit representation of the hour in 12-hour format 01 through 12 %j Day of the year, 3 digits with leading zeros 001 to 366 %m Two digit representation of the month 01 (for January) through 12 (for December) %M Two digit representation of the minute 00 through 59 %p Upper-case 'AM' or 'PM' based on the given time AM for 00:31, PM for 22:23 %S Two digit representation of the second 00 through 59 %w Numeric representation of the day of the week 0 (for Sunday) through 6 (for Saturday) %W A numeric representation of the week of the year, starting with the first Monday as the first week 46 (for the 46th week of the year beginning with a Monday) %x Date representation 11/01/14 %X Time representation 03:59:16 or 15:59:16 %y Two digit representation of the year (*) 09 %Y Four digit representation for the year (*) 2038 %Z The time zone abbreviation. EST for Eastern Time %% A literal percentage character ("%") (*) A %Y or %y after a %W format code the year is adjusted as necessary. %W.%Y returns the correct year for the calendar week.
- DAYNAME(date)
- DAYNAME returns of the weekday name for the
specified date.
DAYNAME(DATE(2014,11,4)) » Tuesday
- DAYOFMONTH(date)
- DAYOFMONTH returns the day of the month (1 to 31)
for the specified date.
DAYOFMONTH(DATE(2014,11,4)) » 4
- DAYOFWEEK(date)
- DAYOFWEEK returns the day of the week (1 to 7)
for the specified date.
A value of 1 represents Sunday, 2 Monday, ... 7 Saturday.
DAYOFMONTH(DATE(2014,11,4)) » 3
- DAYOFYEAR(date)
- DAYOFYEAR returns the day of the year (1 to 366)
for the specified date.
DAYOFYEAR(DATE(2014,11,4)) » 308
- HOUR(time)
- HOUR returns the hour for the given time or
timestamp. An integer between 0 to 23 is returned.
HOUR(NOW()) » 16
- MINUTE(time)
- MINUTE returns the minute for the given time
or timestamp. Returns an integer in the range of 0 to 59.
MINUTE(NOW()) » 3
- MONTH(date)
- MONTH returns the month for the given date
as an integer value in the range of 1 to 12.
MONTH(DATE(2014,11,4)) » 11
- MONTHNAME(date)
- MONTHNAME returns the name of the month for
the given date.
MONTHNAME(DATE(2014,11,4)) » November
- NOW()
- NOW returns the current date and time.
- QUARTER(date)
- QUARTER returns the quarter of the specified
date as an integer in the range of 1 to 4,
where 1 represents January 1 through March 31.
QUARTER(DATE(2014,11,4)) » 4
- QUARTERBEG(date)
- QUARTERBEG returns a date value with the first
day of the quarter with the specified date.
QUARTERBEG(DATE(2014,11,4)) » 2014-10-01
- SECOND(time)
- SECOND returns the seconds for the given time.
Returns an integer in the range of 0 to 59.
SECOND(NOW()) » 12
- TIME(hour, minute, seconds)
- TIME returns a time value from the given arguments.
DATEFMT("%H:%M:%S", TIME(13,45,0)) » 13:45:00
- TIMESTAMPADD(interval, count, timestamp)
- TIMESTAMPADD returns a new timestamp value by
adding an interval to an existing timestamp value.
Interval is one of the following constants: SQL_TSI_FRAC_SECOND,
SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY,
SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER or SQL_TSI_YEAR
TIMESTAMPADD(SQL_TSI_WEEK,-4,DATE(2014,11,25)) » 2014-10-28
- TIMESTAMPDIFF(interval, timestamp1, timestamp2)
- TIMESTAMPDIFF returns the difference (in the
specified interval) between two timestamp values. Interval is one of
the following constants: SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND,
SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK,
SQL_TSI_MONTH, SQL_TSI_QUARTER or SQL_TSI_YEAR
TIMESTAMPDIFF(SQL_TSI_DAY,DATE(2014,10,1),DATE(2014,11,25)) » 55
- WEEK(date)
- WEEK returns the week number for the
specified date as an integer in the range of 1 to 53.
WEEK(DATE(2014,10,1)) » 40
- WEEKBEG(date)
- WEEKBEG returns the first day of the week
(Monday) from the specified date.
WEEKBEG(DATE(2014,10,1)) » 2014-09-29
- YEAR(date)
- YEAR returns the year of the passed date as an
integer.
YEAR(NOW()) » 2014
- YEARBEG(date)
- YEARBEG returns the first day of the year from
the specified date as a date value.
YEARBEG(NOW()) » 2014-01-01
Conversion Functions
- CONVERT(argument, type)
- CONVERT converts the argument to the specified
data type.
Type must be a SQL type name, such as SQL_CHAR, SQL_INTEGER, SQL_FLOAT,
SQL_DOUBLE, SQL_DATE, SQL_TIME, SQL_TIMESTAMP.
CONVERT('2014-11-28', SQL_DATE) » 2014-11-28
The following conversions are supported:
- SQL_CHAR
A numeric argument is converted to a string. A date argument is converted to a format of "yyyy-mm-dd". A time argument is converted to a format of "HH:MM:SS". A timestamp argument is converted to a format of "yyyy-mm-dd HH:MM:SS". - SQL_INTEGER
A string argument must contain a valid integer number. A numeric value is converted to an integer value (truncated). A date, time or timestamp value is converted to seconds representing the number of seconds since the epoch or the start of the day. - SQL_FLOAT, SQL_DOUBLE
A string argument must contain a valid floating point number. A numeric value is converted to a floating point value. A date, time or timestamp value is converted to seconds. - SQL_DATE
A string argument must contain a valid date in the form "yyyymmdd", "yyyy-mm-dd", "mm/dd/yyyy", "dd.mm.yyyy" or "dd-mon-yyyy" (where mon is a three character abbreviated month name, such as JAN, FEB, ... DEC). A year below 1900 is understood to be relative to 1900.
A numeric argument specifies seconds. - SQL_TIME
A string argument must contain a valid time in the form [[hh]mm]ss or hh:mm[:ss]. A numeric argument specifies the number of seconds since midnight. - SQL_TIMESTAMP
A string argument must contain a valid date and optional time. A numeric argument specifies seconds.
- SQL_CHAR
System Functions
- IFNULL(expression, value)
- IFNULL replaces a NULL values with the
given value. The data type of value must be compatible with the
data type of the expression.
IFNULL(NULL,'missing') » missing
- IF(condition, expression1, expression2)
- IF returns expression1 if the condition is true,
expression2 for false.
IF(1+1=3,'yes','no') » no
SQL Aggregate Functions
SQL Aggregate functions calculate a single value from multiple rows. Aggregate functions are only valid in a SELECT list and the HAVING clause. Aggregate functions ignore NULL values.If you use an aggregate function in your query then all column expressions must be an aggregate function, an expression in the GROUP BY clause or a constant expression.
- COUNT(* | expression)
- COUNT returns the number of non-NULL items in a group.
Without a GROUP BY clause it will count the number of rows of the SELECT.
- SUM(expression)
- SUM returns the sum of numeric values of items in a group.
Without a GROUP BY clause it will aggregate with all rows of the SELECT.
If there is no non-NULL value in a group then it returns NULL.
- MAX(expression)
- MAX returns the maximum value of items in a group.
Without a GROUP BY clause it will aggregate all rows of the SELECT.
- MIN(expression)
- MIN returns the minimum value of items in a group.
Without a GROUP BY clause it will aggregate all rows of the SELECT.
- AVG(expression)
- AVG returns the average value of items in a group.
Without a GROUP BY clause it will return the average of all rows.
This is equal to SUM( x ) / COUNT( x ).