In addition to field values and constants, there are a number of functions that may be used within SQL expressions. Each function requires a fixed number of ‘arguments’ to be passed to it and returns one value.
The functions are as follows:
Conditional expression
IIF
IIF(condition,first alternative, second alternative)
The IIF function returns the value of the second parameter if the first expression evaluates as true, otherwise returns the third. e.g.
IIF(service_total_score>total_score,service_total_score,total_score)>20 will select those objects with the maximum of the service_total_score and the total_score being greater than 20
NVL
NVL(expr1,expr2)
The NVL function returns the value of expr2 if expr1 is null. If expr1 is not null, then NVL returns expr1.
Numerical
Functions relating to conversion of floating point numbers to integers and taking of absolute values.
ABS
ABS(number)
Calculates the absolute value of a number i.e. if the number is less than zero returns negative 1 times the number, turning it positive e.g. ABS(-3) = 3. If the number is zero or above it is unchanged.
e.g. SET user_number_1 = ABS(ground_level)
INT
INT(number)
INT takes the integer part of a number. If the number is an integer it will remain unchanged.
FLOOR
FLOOR(number)
FLOOR returns the closest integer less than or equal to its parameter. Like INT it leaves integers unchanged. The difference between INT and FLOOR is that, whilst they will return the same value for positive numbers, INT will, by removing the fractional part of a number, make negative numbers larger (e.g. INT(-123.45) will be 123). FLOOR, on the other hand will make negative numbers more negative (e.g. FLOOR(-123.45) will be –124.
CEIL
CEIL(number)
CEIL returns the closed integer greater than or equal to its parameter. Like INT it leaves integers unchanged.
Number conversion and formatting
FIXED
FIXED(nyumber to convert,number of decimal places)
Given a number and a number of decimal places from 0 to 8, convert the number to a string with that number of decimal places, rounding up or down as appropriate. If the number of decimal places is 0 the string will have no decimal point.
e.g.
FIXED(1.9,3) is "1.900"
FIXED(1.9991,3) is "1.999"
FIXED(1.9999,3) is "2.000"
String manipulation
Functions relating to the manipulation of strings.
LEN
LEN(string)
Returns the length of a string i.e. how many characters long the string is, so LEN(‘MYNODEID’) would be 8. This is often used in conjunction with other string manipulation functions described below. The function LEN may also be used to find the length of a list variable. List variables and the meaning of the LEN function in that context are described later in the paper.
LEFT
LEFT(string,number of characters)
Returns a string containing the first n characters in a string, where n is the second parameter passed to the function e.g. LEFT(‘MX11112222’,2) would be ‘MX’. If the second parameter is zero or less, the empty string ‘’ is returned. On the other hand, if the number is equal to or greater than the number of characters in the string the whole string is returned.
RIGHT
RIGHT(string, number of characters)
Returns a string containing the last n characters in a string, where n is the second parameter passed to the function e.g. RIGHT(‘MX11112222’) would be ‘11112222’. If the second parameters is zero or less, the empty string ‘’ is returned. On the other hand, if the number is equal to or greater than the number of characters in the string the whole string is returned.
MID
MID(string,start position, number of characters)
Returns a string containing a number of characters from a string starting at a given position. The positions start from 1 for the first character, with 2 for the second etc. so MID(‘MX11112222’,1,2) giving ‘MX’, MID(‘MX11112222’,3,4) giving ‘1111’ and MID(‘MX11112222’,7,4) giving ‘2222’.
If the second parameter is 0 or less or greater than the number of characters in the string then the empty string is returned. If the third parameter is 0 or less than the empty string is returned. If the number of characters given in the third parameter is greater than the number of characters remaining starting from the second parameter, then the rest of the string is returned e.g. MID(‘MX12345678’,9,999) would return ‘78’, in this case the rest of the string starting from the 9th character.
SUBST
SUBST(string,thing to replace, thing to replace it with)
Replaces the first instance of the second parameter in the first with the third e.g. SUBST(‘01880132’,'01','ND') returns 'ND880132'
GSUBST
GSUBST(string, thing to replace, thing to replace it with)
Replaces all instances of the second parameter in the first with the third e.g. GSUBST(‘01880132’,'01','ND') returns 'ND88ND32'
GENSUBST
GENSUBST(string, regexp, format)
Replaces the string with the format defined if regular expression regxp matches the string, otherwise returns the string unchanged. e.g.
SET user_text_1 = GENSUBST(node_id,'SK([0-9]*)','99\1') will set user_text_1 to the node_id unless the node_id matches the regular expression 'SK([0-9]*)’, otherwise will set user_text_1 to 99 followed by the numerical part of the node_id following the SK.
The regular expression may contain a number of bracketed sub-expressions. In this case there is one bracketed sub-expression ([0-9]*). The bracketing does not change what the regular expression will match, so ‘SK([0-9]*)’ will match the same things as will ‘SK[0-9]*’, however each sub-expression is given a number, starting with 1, and the text that matches that sub-expression may be used in the format by using that number preceded with a backslash. In this case what is happening is that a node ID consisting of the characters SK followed by a number of digits is replaced in the result of the function with the digits 99 followed by the string of digits from the node_id e.g. SK12345678 becomes 9912345678.
NL
NL()
The NL function returns a new-line character e.g. to set up a 3 line note field use something like
SET notes='THIS'+NL()+'THAT'+NL()+'THE OTHER'
Date manipulation
Functions relating to the manipulation of dates.
YEARPART
YEARPART(date)
Given a date, returns the year of the date as a number.
MONTHPART
MONTHPART(date)
Given a date, returns the month in the date as a number.
DAYPART
DAYPART(date)
Given a date, returns the day of the date as a number.
DATEPART
DATEPART(date)
Given a date, returns the date part as a date i.e. removes any minutes from the date so the value represents midnight at the beginning of that day.
TIMEPART
TIMEPART(date)
Given a date, returns the time part as a number in minutes e.g. if the date is 01/02/2008 12:34 this returns 754.0 – the number of minutes after midnight
NOW
NOW()
Returns the current date and times as a date.
YEARSDIFF
YEARSDIFF(from,to)
Returns the number of complete years between the two dates as a number, which will be an integer. The calculation is based on midnight on the dates involved i.e. if from is 01/05/2008 at 12:34 and ‘to’ is 01/05/2009 at 12:29, this will count as a full year and therefore return 1.
MONTHSDIFF
MONTHSDIFF(from,to)
Returns the number of complete months between the two dates as a number, which will be an integer. The calculation is based on midnight on the dates involved.
DAYSDIFF
DAYSDIFF(from,to)
Returns the number of complete days between the two dates as a number, which will be an integer. The calculation is based on midnight on the dates involved.
INYEAR
INYEAR(date,number)
Returns true if the date is in the year given as a number e.g.
INYEAR(when_surveyed,1993)
INMONTH
INMONTH(date,month,year)
Returns true if the date is in the year and month given as numbers e.g.
INMONTH(when_surveyed,3,1993)
INYEARS
INYEARS(date,startyear,endyear)
Returns true if the date is in a year between the start and end years inclusive e.g.
INYEARS(when_surveyed,1993,1995)
INMONTHS
INMONTHS(date,startmonth,startyear,endmonth,endyear)
Returns true if the date is between the start month in the start year and the end month in the end year inclusive e.g.
INMONTHS(when_surveyed,10,1993,2,1994)
If any of the parameters to these functions are not numbers the function returns false. If the number is not an integer, the number is rounded to the nearest integer.
ISDATE
ISDATE(putative_date)
If the field is a date because it has come from the database returns true, if it is a string then returns true if it can be converted into a date, otherwise returns false.
MONTHYEARPART
MONTHYEARPART(date)
This function returns the string "<month>/<year>" e.g. "01/2010". As you can see here, if the month is January - September you get a 0 prefix.
YEARMONTHPART
YEARMONTHPART(date)
This function returns the string "<year>/<date>" e.g. "2001/01". As you can see here, if the month is January - September you get a 0 prefix. The aim here is to have dates that can easily be sorted.
MONTHNAME
MONTHNAME(date)
This function returns the name of the month (in the current locale).
SHORTMONTHAME
SHORTMONTHAME(date)
This function returns the abbreviated name of the month (as determined by the locale and how Windows abbreviates it).
DAYNAME
DAYNAME(date)
This function returns the name of the day (in the current locales).
SHORTDAYNAME
SHORTDAYNAME(date)
This function returns the abbreviated day name (as determined by the locale and how Windows abbreviates it).
NUMTOMONTHNAME
NUMTOMONTHNAME(n)
Returns the month name given an integer from 1 to 12.
NUMTOSHORTMONTHNAME
NUMTOSHORTMONTHNAME(n)
Returns the short version of a month name (e.g. Jan) given an integer from 1 to 12.
TODATE
TODATE(year,month,day)
Returns the date given the year, month and day as integers.
TODATETIME
TODATETIME(year,month,day,hours,minutes)
Returns the date given the year, month, day, hours and minute as integers.
Mathematical
Trigonometric functions, logs and exponents. All these functions return NULL if their parameters cannot be converted into numbers. There may be further restrictions on the parameters as described below. All angles in the trigonometric functions are expressed in degrees.
LOG
LOG(x)
Calculates the log (base 10) of x. Returns NULL if x<=0.
LOGE
LOGE(x)
Calculates the log (base e) of x, otherwise known as a 'natural logarithm'. Returns NULL if x<=0.
EXP
EXP(x)
Calculates e^x.
SIN
SIN(x)
Calculates the sin of x.
COS
COS(x)
Calculates the cosine of x.
TAN
TAN(x)
Calculates the tangent of x. Returns NULL if cos(x) = 0 (and therefore tan(x) would be infinite).
ASIN
ASIN(x)
Calculates the inverse sin of x. Returns NULL if x is less than -1 or greater than 1. Returns a value within the range -90 degrees to 90 degrees.
ACOS
ACOS(x)
Calculates the inverse cosine of x. Returns NULL if x is less than -1 or greater than 1. Returns a value within the range 0 to 180 degrees.
ATAN
ATAN(x)
Calculates the inverse tangent of x. Returns a value within the range -90 degrees to 90 degrees.
ATAN2
ATAN2(x,y)
Calculates the inverse tangent of x / y using the signs of x and y to correctly determine the quadrant. Returns a value within the range -180 degrees to 180 degrees.
GAMMALN
GAMMALN(x)
Returns the LOG (base e) of the Gamma function of x.