BearDB SQL Functions


SQL Functions can be used in queries, either as part of the SELECT clause or the WHERE clause.

SQL Functions names are CASE SENSITIVE, and must be in UPPER-CASE as documented below:

COUNT(p1) : NUMBER

Use: SELECT Clause, WHERE Clause
Where: p1 must be a single column name.
Type: Aggregate Function.
Sample:  
 SELECT COUNT(column1) FROM tableName WHERE column2 = 'a value'


CONCAT(p1,p2) COMPARATOR p3 : STRING

Use: SELECT Clause, WHERE Clause
Where: p1, p2, and p3 can be column name, value, or SQL Function.
Type: Standard Function.
Sample:  
 SELECT col1, col2, col3 FROM tableNameA WHERE CONCAT(col2, 'word') = 'a value'


CONTAINS(p1,p2) : BOOLEAN

Use: WHERE Clause
Where: p1 can be column name or value, p2 can be column name, value, or SQL Function.
Type: Standard Function.
Sample:  
 SELECT col1, col3 FROM ourTable WHERE CONTAINS(col2, 'a phrase')


CALC(p1,p2,OPERATOR) COMPARATOR p3 : NUMBER

Use: SELECT Clause, WHERE Clause
Where: p1, p2 can be column name, value, or SQL Function. p3 can be column name or value.
  OPERATOR must be one of: ADD, SUBTRACT, MULTIPLY, DIVIDE.
Type: Standard Function.
Sample:  
 SELECT col1, col3 FROM ourTable WHERE CALC(col2, 405, ADD) = 7154
 SELECT columnA, colQ FROM ourTable WHERE CALC(col5, 2, MULTIPLY) = col7


DISTINCT(p1) : RESULT

Use: SELECT Clause
Where: p1 must be a single column name.
Type: Standard Function.
Sample:  
 SELECT DISTINCT(column1) FROM tableName WHERE column2 = 'a value'


LENGTH(p1) COMPARATOR p2 : NUMBER

Use: SELECT Clause, WHERE Clause
Where: p1 can be column name, p2 can be column name or value or SQL Function.
Type: Standard Function.
Sample:  
 SELECT col1, col3 FROM ourTable WHERE CONTAINS(col2, 'a phrase')


MIN(p1) COMPARATOR p2 : NUMBER

Use: SELECT Clause, WHERE Clause
Where: p1 can be column name, p2 can be column name or value or SQL Function.
Type: Aggregate Function.
Sample:  
 SELECT MIN(col2) FROM ourTable WHERE col3 = 'special value'


MAX(p1) COMPARATOR p2 : NUMBER

Use: SELECT Clause, WHERE Clause
Where: p1 can be column name, p2 can be column name or value or SQL Function.
Type: Aggregate Function.
Sample:  
 SELECT MAX(col2) FROM ourTable GROUP BY col2


ROUND(p1) COMPARATOR p2 : NUMBER

Use: WHERE Clause
Where: p1 and p2 can be column name or value or SQL Function.
Type: Standard Function.
Sample:  
 SELECT col1, col3 FROM ourTable WHERE ROUND(col2) > 12000


SUBSTR(p1,startIndex,length) COMPARATOR p4 : STRING

Use: WHERE Clause
Where: p1 can be column name or value. startIndex must be a number. length is optional, but must be a number. p4 can be column name, value, or SQL Function.
Type: Standard Function.
Sample:  
 SELECT columnT, Phone FROM ourTable WHERE SUBSTR(columnB, 2, 4) = 'this'
 SELECT col45, dataColumn FROM ourTable WHERE SUBSTR(col5, 2) = 'sample data value'


SUM(p1) COMPARATOR p2 : NUMBER

Use: SELECT Clause, WHERE Clause
Where: p1 can be column name, p2 can be column name or value or SQL Function.
Type: Aggregate Function.
Sample:  
 SELECT SUM(col2) FROM ourTable GROUP BY col2


TRIM(p1) COMPARATOR p2 : STRING

Use: SELECT Clause, WHERE Clause
Where: p1 can be column name or value, p2 can be column name or value or SQL Function.
Type: Standard Function.
Sample:  
 SELECT col1, col3 FROM ourTable WHERE TRIM(col2) = 'a phrase'