SQL/R A.03.00 Release Notes

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.

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.

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

COUNT
SUM
MAX
MIN
AVG

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 ).