DBLX 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


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'


 
 

DBLX SQL Guide