SQL Functions

Functions can be embedded SQL, used in formulas or called separately. This section covers the variety of functions that are supported by OpenBase.

Calling Functions Separately

For those times when you need to use a database function to perform an operation that is not connected to a table, use the function command. The function command can take any combination of functions and calculations. Here are some examples:

openbase 1> function 1+1
openbase 2> go
RESULT = 2
openbase 1> function now()
openbase 2> go
RESULT = 2004-05-23 10:43:07 -0500

Using Functions in SQL

Functions can be used almost everywhere within SQL statements. Most functions can not be used on BLOBs (which include TEXT and OBJECT types).

Using Functions in Constraints

We recommend that you don’t use a function on the left side of a constraint in the WHERE specification of an SQL select. This would bypass the use of any indexes and make searching slower.

Here is an example of what NOT to do. In this case the user is trying to make this a case-insensitive match by forcing name to upper case.

SELECT * FROM foo WHERE UPPER(name) = “NORMAN” <--- DO NOT TO THIS

This statement will bypass the index and make the search much more costly. Instead a statement like this could be done without a function and still ignore case:

SELECT * FROM foo WHERE name LIKE “NORMAN” <--- DO THIS

Type Conversion Functions

Value conversion functions provide a method to convert values between SQL types and control the return type of calculations.

Function Description

TOCHAR(value) Converts value into a character string. The value parameter can be of any type.

TODOUBLE(value) Converts value to a double value. The value parameter can be of any type.

TOINT(value) Converts value to an integer value. The value parameter can be of any type.

TOLONG(value) Converts value to a long value. The value parameter can be of any type.

TOLONGLONG(value) Converts value to a long long value. The value parameter can be of any type.

TOMONEY(value) Converts value to a money value. The value parameter can be of any type.

String Manipulation Functions

This section describes the SQL functions that can be performed on strings. These include:

SQL String Functions

  • LENGTH Page
  • INDEXOF Page
  • REPLACE Page
  • SUBSTRING Page
  • UPPER Page
  • RIGHT Page
  • LEFT Page
  • TRIM Page
  • PROPER Page
  • IF Page
  • CHOOSE Page

*

ASCII(string)

Returns an integer representing the ASCII code value of the leftmost character in string.

CONCAT(string1, string2)

Returns a new character string formed by concatenating string1 and string2.

SELECT CONCAT(TITLE, TITLE)from MOVIE

DIFFERENCE(string1, string2)

Returns an integer indicating the difference between the values returned by the SOUNDEX function for string1 and string2. The SOUNDEX function analyses the sounds that words make.

FETCHBLOB(blob_key)

Returns the first 4K of a text blob and allows it to be used inside an SQL operation. Longer blob text is truncated by the function. If the MOVIE table were to have a COMMENT column of type TEXT, the following SQL would work:

SELECT TITLE, FETCHBLOB(COMMENT) from MOVIE

FIGGSCORE(text, keywords)

The FIGG Score takes a text string and assigns a score to it based on how well it matches the keys passed into it. The higher the score, the closer the match.

SELECT TITLE, FIGGSCORE(TITLE,”Star”) from MOVIE

FIGGSCOREBLOB(blob_key, keywords)

The FIGG Score takes a text string and assigns a score to it based on how well it matches the keys passed into it. The higher the score, the closer the match.

This version of the function takes a blob key as its first parameter and scores the entire Blob text , regardless of length.

SELECT TITLE, FIGGSCOREBLOB(COMMENT,”Star”) from MOVIE

INSERT(string, startpos, length, replacestring)

Inserts replacestring into string at startpos and replaces length characters. The replacestring is not truncated by length. The entire replacement string is inserted at the specified point.

Example: Select all movie titles and replace the first word with BLANK

SELECT INSERT(TITLE, 0, INDEXOF(TITLE,' '), 'BLANK'), TITLE from MOVIE
    where INDEXOF(TITLE,' ') >= 0

INDEXOF(string, substring)

Returns the numerical index of substring in string or -1 if the substring cannot be found.

Example: Select all movie titles which have the word the in the title.

SELECT INDEXOF(TITLE,'the'), TITLE FROM MOVIE WHERE INDEXOF(TITLE,'the') >= 0

LCASE(string), LOWER(string)

LCASE and LOWER both convert string to lower case. Example: Select movie titles in lower case.

SELECT LOWER(TITLE) from MOVIE

LEFT(string, length)

Returns the left portion of string with length characters. Example: Select the first 5 characters of all movie titles.

SELECT LEFT(TITLE,5) from MOVIE

LENGTH(string)

Returns the length of string.

Example: Bring back all of the movies which have a title longer than 10 characters.

SELECT LENGTH(TITLE), TITLE FROM MOVIE WHERE LENGTH(TITLE) > 10

LOCATE(matchString, sourceString [, start])

Returns the position of matchString in sourceString. If start is specified it returns the first occurrence of matchString in sourceString after position start. 0 is returned if sourceString does not contain matchString. A position of 1 represents the first character of sourceString.

Example: Locate the position of the word THE in each movie title.

SELECT LOCATE(The , TITLE), TITLE FROM MOVIE

LTRIM(string)
Returns a string with all blank spaces trimmed from the left side of string. Example: Trim the spaces from the left side of the movie titles.

SELECT LTRIM(    + TITLE), TITLE FROM MOVIE

REPEAT(string, count)

Returns string repeated count time. The result is concatenated. Example: Add 5 dashes to the beginning of each movie title.

SELECT REPEAT(- , 5) + TITLE FROM MOVIE

REPLACE(source, match, replaceString)

Replaces all occurrences of match in source with replaceString and returns the new string.
Example: Select all Movie titles and replace The with the word REPLACE .

SELECT REPLACE(TITLE, The , REPLACE ), TITLE from MOVIE

RIGHT(string, length)

Returns the right portion of string with length characters. Example: Select the right 5 characters of all movie titles.

SELECT RIGHT(TITLE,5) from MOVIE

RTRIM(string)

Returns a string with all blank spaces trimmed from the right side of string. Example: Trim the spaces from the right side of the movie titles.

SELECT RTRIM(TITLE +    ), TITLE FROM MOVIE

SOUNDEX(string)

Returns a character string representing the sound of the words in string. Normally this is a four-digit SOUNDEX code, a phonetic representation of each word.

Example: Select the SOUNDEX code for each of the movie titles.

SELECT SOUNDEX(TITLE) from MOVIE

SPACE(count)

Returns a character string of count spaces.

Example: Select TITLE twice from MOVIE and add 5 spaces between them.

SELECT TITLE + SPACE(5)+ TITLE from MOVIE

SUBSTRING(string, startpos, length)

Returns the substring of string starting at startpos and including length characters.

Example: Select the first three characters of all movie titles.

SELECT SUBSTRING(TITLE,0,3) from MOVIE

UPPER(string), UCASE(string)

Converts string to upper case. Example: Select movie titles in upper case.

SELECT UPPER(TITLE) from MOVIE

TRIM(string)

Removes extra blank spaces from string.

SELECT TRIM(TITLE) from MOVIE

PROPER(string)

Capitalizes the first letter of each word in string. Example: Show all movie titles with proper capitalization.

SELECT PROPER(TITLE) from MOVIE

IF(condition, returnValueIfTrue, retValueIfFalse)

Returns conditional values depending on the expression condition. All values and expressions can be made up of multiple elements.

Example: Return the movie title or the string Smaller Than 10 for movie titles that are shorter than 10 characters.

SELECT IF( (LENGTH(TITLE) < 10), 'Smaller Than 10', TITLE) FROM MOVIE

CHOOSE(number, value1, value2,…).

Returns the value in the location indicated by number. Values may be constants, column names, expressions or any combination of these.

Example: Return value1 for each of the movie titles. 1 could be replaced by a calculation or database column.

SELECT CHOOSE(1, value0 , value1 , value2 ), TITLE FROM MOVIE

Set Operations

A varchar, or char type, can be used to collect a set of related record keys for fast searching. It can be used for a variety of things, including: connecting a small set of group or category choices to a record; specifying user access to records; or specifying record options, which may be configurable in another table.

While all of these operations can also be done using one-to-many relationships, using a Set will de-normalize data, reduce the number of joins and improve performance.

A Set is represented as a collection of numbers that might represent option numbers or primary keys from another table. Set operations create strings in numerically ascending order that look like this:

|50||600||1000|

If you want to search for a record that has both 50 and 1000 as members, you can search for them using a LIKE operator as follows:

SELECT * FROM contact WHERE filter LIKE “*|50|*|1000|*”

It is recommended that your set column, in this example called filter , is indexed. This will provide the fastest searching possible.

The following functions make it easy to add and remove a set value from a series of records.

AddMemberToSet(existing-set-string, new-member)

The AddMemberToSet() function provides a way to add a new member to the existing-set-string. If added, the new set string is returned. If the member exists, the existing-set-string is returned.

update contact set filter = AddMemberToSet(filter,"206") WHERE ...

RemoveMemberFromSet(existing-set-string, remove-member)

The RemoveMemberFromSet() function will remove the member from the set string, if it exists, and return the new set string.

update contact set filter = RemoveMemberFromSet(filter,"206") WHERE ...

Date Manipulation Functions

This section describes the SQL functions that can be performed on dates. These include:

CURDATE()
Returns the current date as a date type value.

CURTIME()
Returns the current local time as a time type value.

DAYNAME(date)
Returns a character string representing the day name component of date. The parameter can be either a date type or a datetime type.

DAYOFMONTH(date)
Returns an integer from 1 to 31 representing the day of the month in date. The parameter can be either a date type or a datetime type.

DAYOFWEEK(date)
Returns an integer from 1 to 7 representing the day of the week in date. The number 1 represents Sunday. The parameter can be either a date type or a datetime type.

DAYOFYEAR(date)
Returns an integer from 1 to 366 representing the day of the year in date. The parameter can be either a date type or a datetime type.

HOUR(time)
Returns an integer from 0 to 23 representing the hour component of time. The parameter can be either a time type or a datetime type.

MINUTE(time)
Returns an integer from 0 to 59 representing the minute component of time. The parameter can be either a time type or a datetime type.

MONTH(date)
Returns an integer from 1 to 12 representing the month component of date. The parameter can be either a date type or a datetime type.

MONTHNAME(date)
Returns a character string representing the month name component of date. The parameter can be either a date type or a datetime type.

NOW()
Returns a datetime value representing the current date and time.

QUARTER(date)
Returns an integer from 1 to 4 representing the quarter in date. The number 1 represents the first quarter (January 1 through March 31.) The parameter can be either a date type or a datetime type.

SECOND(time)
Returns an integer from 0 to 59 representing the second component of time. The parameter must be a datetime type.

TIMESTAMPADD(interval, count, timestamp)
Returns a timestamp calculated by adding count number of interval(s) to timestamp. The interval may be one of the following:

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
SQL_TSI_YEAR

TIMESTAMPDIFF(interval, timestamp1,timestamp2)
Returns an integer representing the number of intervals by which timestamp2 is greater than timestamp1. The interval may be one of the following:

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
SQL_TSI_YEAR

WEEK(date)
Returns an integer from 1 to 53 representing the week of the year in date. The parameter can be either a date type or a datetime type.

YEAR(date)
Returns an integer representing the year component of date. The parameter can be either a date type or a datetime type.

Express String Values

There are a few different ways to express string values in your SQL statements. Traditionally, an SQL string must be enclosed in single or double quotes. Here is an example using a table that keeps track of messages:

insert into messages (note) values ('Joe Called')

The string “ Joe Called” is enclosed in quotes to tell OpenBase that it is a string value. However, sometimes you will need to store a string that has quotes in it. You have the choice of handling this in one of a few ways. Here is a description of the standard way:

insert into messages (note) values ('Joe said he can”t call back')
or
insert into messages (note) values ('Joe said he can\’t call back')

This method requires you to programmatically insert a backslash \ or a second quote for each instance of a quote in your string that matches the boundary quotes.

Other Numeric Functions

COS(n), SIN(n), TAN(n), ACOS(n), ASIN(n), ATAN(n)
Returns the cosine, sin, tan, acos, asin and atan of the value of n.
ABS(n)
Returns the absolute value of n.
ROUND(number, decimal)
Returns a number rounded to the decimal places specified.

function round(1234.1234567, 3)
RESULT=1234.12300000

ROUNDANDFORMAT(number, decimal)
Returns a number rounded to the decimal places specified and formatted using an standard sprintf parameter. This example eliminates the trailing zeros.

function ROUNDANDFORMAT(1234.1234567, 3,"%.*f")
RESULT=1234.123
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License