SQL Expressions

An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value. You can use expressions in the Where, Having, and Order By clauses of Select statements; and in the Set clauses of Update statements.

Expressions enable you to use mathematical operations as well as character string manipulation operators to form complex queries.

The Salesforce driver supports both unquoted and quoted identifiers. An unquoted identifier must start with an ASCII alpha character and can be followed by zero or more ASCII alphanumeric characters. Unquoted identifiers are converted to uppercase before being used.

Quoted identifiers must be enclosed in double quotation marks (""). A quoted identifier can contain any Unicode character including the space character. The Salesforce driver recognizes the Unicode escape sequence \uxxxx as a Unicode character. You can specify a double quotation mark in a quoted identifier by escaping it with a double quotation mark.

The maximum length of both quoted and unquoted identifiers is 128 characters.

Valid expression elements are:

■	Column names

■

Literals

■

Operators

■

Functions

Column Names

The most common expression is a simple column name. You can combine a column name with other expression elements.

Literals

Literals are fixed data values. For example, in the expression PRICE * 1.05, the value 1.05 is a constant. Literals are classified into types, including the following:

■

Binary

■	Character string

■

Date

■	Floating point

■

Integer

■

Numeric

■

Time

■

Timestamp

Table 10-4 describes the literal format for supported SQL data types.

Table 10-4. Literal Syntax Examples 

SQL Type	Literal Syntax	Example
BIGINT	n where n is any valid integer value in the range of the INTEGER data type	12 or -34 or 0
BOOLEAN	Min Value: 0 Max Value: 1	0 1
DATE	‘yyyy-mm-dd’	'2010-05-21'
DATETIME	‘yyyy-mm-dd hh:mm:ss.SSSSSS'	'2010-05-21 18:33:05.025'
DECIMAL	n.f where: n is the integral part f is the fractional part	0.25 3.1415 -7.48
DOUBLE	n.fEx where: n is the integral part f is the fractional part x is the exponent	1.2E0 or 2.5E40 or -3.45E2 or 5.67E-4
INTEGER	n where n is a valid integer value in the range of the INTEGER data type	12 or -34 or 0
LONGVARBINARY	‘hex_value’	'000482ff'
LONGVARCHAR	‘value’	'This is a string literal'
TIME	‘hh:mm:ss’	'18:33:05'
VARCHAR	‘value’	'This is a string literal'

Character String Literals

Text specifies a character string literal. A character string literal must be enclosed in single quotation marks. To represent one single quotation mark within a literal, you must enter two single quotation marks. When the data in the fields is returned to the client, trailing blanks are stripped.

A character string literal can have a maximum length of 32 KB, that is, (32*1024) bytes.

Examples

'Hello'

'Jim''s friend is Joe'

Integer Literals

Integer literals are represented by a string of numbers that are not enclosed in quotation marks and do not contain decimal points.

NOTE: Integer constants must be whole numbers; they cannot contain decimals.

Optionally, the integer literals can start with sign characters (+/-)

Examples

1994

-2

Numeric Literals

Unquoted numeric values are treated as numeric literals. If the unquoted numeric value contains a decimal point or exponent, it is treated as a real literal; otherwise, it is treated as an integer literal.

Example

+1894.1204

Binary Literals

Binary literals are represented with single quotation marks. The valid characters in a binary literal are 0-9, a-f, and A-F.

Example

'00af123d'

Date/Time Literals

Date and time literal values are:

■	A Date literal is enclosed in single quotation marks (' '). The format is yyyy-mm-dd.

■	A Time literal is enclosed in single quotation marks (' '). The format is hh:mm:ss.

■	A Timestamp is enclosed in single quotation marks (' '). The format is yyyy-mm-dd hh:mm:ss.SSSSSS.

Operators

This section describes the operators that can be used in SQL expressions.

Unary Operator

A unary operator operates on only one operand.

Grammar

operator operand

Binary Operator

A binary operator operates on two operands.

Grammar

operand1 operator operand2

If an operator is given a null operand, the result is always null. The only operator that does not follow this rule is concatenation (||).

Arithmetic Operators

You can use an arithmetic operator in an expression to negate, add, subtract, multiply, and divide numeric values. The result of this operation is also a numeric value. The + and - operators are also supported in date/time fields to allow date arithmetic. Table 10-5 lists the supported arithmetic operators.

Table 10-5. Arithmetic Operators 

Operator	Purpose	Example
+ -	Denotes a positive or negative expression. These are unary operators.	SELECT * FROM emp WHERE comm = -1
* /	Multiplies, divides. These are binary operators.	UPDATE emp SET sal = sal + sal * 0.10
+ -	Adds, subtracts. These are binary operators.	SELECT sal + comm FROM emp WHERE empno > 100

Concatenation Operator

The concatenation operator manipulates character strings. Table 10-6 lists the only supported concatenation operator.

Table 10-6. Concatenation Operator

Operator	Purpose	Example
||	Concatenates character strings.	SELECT 'Name is' || ename FROM emp

The result of concatenating two character strings is the data type VARCHAR.

Comparison Operators

Comparison operators compare one expression to another. The result of such a comparison can be TRUE, FALSE, or UNKNOWN (if one of the operands is NULL). The Salesforce driver considers the UNKNOWN result as FALSE.

Table 10-7 lists the supported comparison operators.

Table 10-7. Comparison Operators 

Operator	Purpose	Example
=	Equality test.	SELECT * FROM emp WHERE sal = 1500
!= <>	Inequality test.	SELECT * FROM emp WHERE sal != 1500
> <	“Greater than" and "less than" tests.	SELECT * FROM emp WHERE sal > 1500 SELECT * FROM emp WHERE sal < 1500
>= <=	“Greater than or equal to" and "less than or equal to" tests.	SELECT * FROM emp WHERE sal >= 1500 SELECT * FROM emp WHERE sal <= 1500
[NOT] IN	“Equal to any member of" test.	SELECT * FROM emp WHERE job IN ('CLERK','ANALYST') SELECT * FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30)
[NOT] BETWEEN x AND y	"Greater than or equal to x" and "less than or equal to y."	SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000
EXISTS	Tests for existence of rows in a subquery.	SELECT empno, ename, deptno FROM emp e WHERE EXISTS (SELECT deptno FROM dept WHERE e.deptno = dept.deptno)
IS [NOT] NULL	Tests whether the value of the column or expression is NULL.	SELECT * FROM emp WHERE ename IS NOT NULL SELECT * FROM emp WHERE ename IS NULL
ESCAPE clause in LIKE operator LIKE ’pattern string’ ESCAPE ’c’	The Escape clause is supported in the LIKE predicate to indicate the escape character. Escape characters are used in the pattern string to indicate that any wildcard character that is after the escape character in the pattern string should be treated as a regular character. The default escape character is backslash (\).	SELECT * FROM emp WHERE ENAME LIKE 'J%\_%' ESCAPE '\' This matches all records with names that start with letter 'J' and have the '_' character in them. SELECT * FROM emp WHERE ENAME LIKE 'JOE\_JOHN' ESCAPE '\' This matches only records with name ’JOE_JOHN’.

Logical Operators

A logical operator combines the results of two component conditions to produce a single result or to invert the result of a single condition. Table 10-8 lists the supported logical operators.

Table 10-8. Logical Operators 

Operator	Purpose	Example
NOT	Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.	SELECT * FROM emp WHERE NOT (job IS NULL) SELECT * FROM emp WHERE NOT (sal BETWEEN 1000 AND 2000)
AND	Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE; otherwise, returns UNKNOWN.	SELECT * FROM emp WHERE job = 'CLERK' AND deptno = 10
OR	Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE; otherwise, returns UNKNOWN.	SELECT * FROM emp WHERE job = 'CLERK' OR deptno = 10

Example

In the Where clause of the following Select statement, the AND logical operator is used to ensure that managers earning more than $1000 a month are returned in the result:

SELECT * FROM emp WHERE jobtitle = manager AND sal > 1000

Operator Precedence

As expressions become more complex, the order in which the expressions are evaluated becomes important. Table 10-9 shows the order in which the operators are evaluated. The operators in the first line are evaluated first, then those in the second line, and so on. Operators in the same line are evaluated left to right in the expression.You can change the order of precedence by using parentheses. Enclosing expressions in parentheses forces them to be evaluated together.

Table 10-9. Operator Precedence 

Precedence	Operator
1	+ (Positive), - (Negative)
2	*(Multiply), / (Division)
3	+ (Add), - (Subtract)
4	|| (Concatenate)
5	=, >, <, >=, <=, <>, != (Comparison operators)
6	NOT, IN, LIKE
7	AND
8	OR

Examples

Example A

The query in the following example returns employee records for which the department number is 1 or 2 and the salary is greater than $1000:

SELECT * FROM emp WHERE (deptno = 1 OR deptno = 2) AND sal > 1000

Because parenthetical expressions are forced to be evaluated first, the OR operation takes precedence over AND.

Example B

In the following example, the query returns records for all the employees in department 1, but only employees whose salary is greater than $1000 in department 2.

SELECT * FROM emp WHERE deptno = 1 OR deptno = 2 AND sal > 1000

The AND operator takes precedence over OR, so that the search condition in the example is equivalent to the expression deptno = 1 OR (deptno = 2 AND sal > 1000).

Functions

The Salesforce driver supports a number of functions that you can use in expressions, as listed and described in Table 10-10 through Table 10-14.

Table 10-10. Numerical Functions Supported 

Numerical Function	Description
ABS(d)	Returns the absolute value of a double value.
ACOS(d)	Returns the arc cosine of an angle.
ASIN(d)	Returns the arc sine of an angle.
ATAN(d)	Returns the arc tangent of an angle.
ATAN2(a,b)	Returns the tangent of a/b.
BITAND(a,b)	Returns a and b.
BITOR(a,b)	Returns a or b.
CEILING(d)	Returns the smallest integer that is not less than d.
COS(d)	Returns the cosine of an angle.
COT(d)	Returns the cotangent of an angle.
DEGREES(d)	Converts radians to degrees.
EXP(d)	Returns e (2.718... raised to the power of d).
FLOOR(d)	Returns the largest integer that is not greater than d.
LOG(d)	Returns the natural logarithm (base e).
LOG10(d)	Returns the logarithm (base 10).
MOD(a,b)	Returns a modulo b.
PI( )	Returns pi (3.1415...).
POWER(a,b)	Returns a raised to the power of b.
RADIANS(d)	Converts degrees to radians.
RAND( )	Returns a random number x bigger or equal to 0.0 and smaller than 1.0.
ROUND(a,b)	Rounds a to b digits after the decimal point.
ROUNDMAGIC(d)	Solves rounding problems such as 3.11-3.1-0.01.
SIGN(d)	Returns -1 if d is smaller than 0, 0 if d==0 and 1 if d is bigger than 0.
SIN(d)	Returns the sine of an angle.
SQRT(d)	Returns the square root.
TAN(A)	Returns the trigonometric tangent of an angle.
TRUNCATE(a,b)	Truncates a to b digits after the decimal point.

Table 10-11. String Functions Supported 

String Function	Description
ASCII(s)	Returns the ASCII code of the leftmost character of s.
BIT_LENGTH(str)	Returns the length of the string in bits.
CHAR(c)	Returns a character that has the ASCII code c.
CHAR_LENGTH(str)	Returns the length of the string in characters.
CONCAT(str1,str2)	Returns the string that results from concatenating str1 + str2.
DIFFERENCE(s1,s2)	Returns the difference between the sound of s1 and s2.
HEXTORAW(s1)	Returns a translated string/.
INSERT(s,start,len,s2)	Returns a string where len number of characters beginning at start has been replaced by s2.
LCASE(s)	Converts s to lower case.
LEFT(s,count)	Returns the leftmost count of characters of s. If s requires double quoting, use SUBSTRING( ) instead.
LENGTH(s)	Returns the number of characters in s.
LOCATE(search,s,[start])	Returns the first index (1=left, 0=not found) where search is found in s, starting at start.
LTRIM(s)	Removes all leading blanks in s.
OCTET_LENGTH(str)	Returns the length of the string in bytes (twice the number of characters).
RAWTOHEX(s1)	Returns translated string.
REPEAT(s,count)	Returns s repeated count times.
REPLACE(s,replace,s2)	Returns s with all occurrences of replace replaced with s2.
RIGHT(s,count)	Returns the right-most count of characters of s.
RTRIM(s)	Removes all trailing spaces in s.
SOUNDEX(s)	Returns a 4-character code representing the sound of s.
SPACE(count)	Returns a string consisting of count spaces.
SUBSTR(s,start[,len])	Alias for substring.
SUBSTRING(s,start[,len])	Returns the substring starting at start (1=left) with length len.
UCASE(s)	Converts s to uppercase.
LOWER(s)	Converts s to lowercase.
UPPER(s)	Converts s to uppercase.

 

Table 10-12. Date/Time Functions Supported 

Date/Time Function	Description
CURDATE( )	Returns the current date.
CURTIME( )	Returns the current time.
DATEDIFF(string, datetime1, datetime2)	Returns the count of units of time elapsed from datetime1 to datetime2. The string indicates the unit of time and can have the following values: ■ 'ms'='millisecond' ■ 'ss'='second' ■ 'mi'='minute' ■ 'hh'='hour' ■ 'dd'='day' ■ 'mm'='month' ■ 'yy' = 'year' Both the long and short form of the strings can be used.
DAYNAME(date)	Returns the name of the day.
DAYOFMONTH(date)	Returns the day of the month (1-31).
DAYOFWEEK(date)	Returns the day of the week (1 means Sunday).
DAYOFYEAR(date	Returns the day of the year (1-366).
HOUR(time)	Returns the hour (0-23).
MINUTE(time)	Returns the minute (0-59).
MONTH(date)	Returns the month (1-12).
MONTHNAME(date)	Returns the name of the month.
NOW( )	Returns the current date and time as a timestamp.
QUARTER(date)	Returns the quarter (1-4).
SECOND(time)	Returns the second (0-59).
WEEK(date)	Returns the week of this year (1-53).
YEAR(date)	Returns the year.
CURRENT_DATE	Returns the current date.
CURRENT_TIME	Returns the current time.
CURRENT_TIMESTAMP	Returns the current timestamp.

 

Table 10-13. System/Connection Functions Supported 

System/Connection Function	Description
DATABASE( )	Returns the name of the database of this connection.
USER( )	Returns the user name of this connection.
CURRENT_USER	SQL standard function, returns the user name of this connection.
CURSESSIONID( )	Returns the ID of the session (connection) on which this function was invoked.
IDENTITY( )	Returns the last identity value that was inserted by this connection.

Table 10-14. System Functions Supported 

System Function	Description
IFNULL(expr,value)	If expr is NULL, then value is returned; otherwise the result of expr is returned. See COALESCE(expr1, expr2, ...) for evaluating multiple expressions.
CONVERT(term,type)	Converts term to another data type.
CAST(term AS type)	Converts term to another data type.
COALESCE(expr1, expr2, ...)	If expr1 is not Null, then it is returned; otherwise, expr2 is evaluated and, if not Null, it is returned, and so on. This is an ANSISQL standard system function.
NULLIF(value1,value2)	If value1 equals value2, then Null is returned; otherwise, value1 is returned.
CASE value1 WHEN value2 THEN value3 [ELSE value4] END	When value1 equals value2, then value3 is returned; otherwise, value4 or Null is returned in the absence of ELSE.
CASE WHEN expr1 THEN value1 [WHEN expr2 THEN value2] [ELSE value4] END	When expr1 is true, then value1 is returned (optionally repeated for more cases); otherwise value4 or Null is returned in the absence of ELSE.
EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE| SECOND} FROM datetime_value)	Any of the date and time terms can be extracted from datetime_value.
POSITION(string_expression1 IN string_expression2)	If string_expression1 is a sub-string of string_expression2, then the position of the sub-string, counting from one, is returned; otherwise, 0 is returned.
SUBSTRING(string_expression FROM numeric_expression1 [FOR numeric_expression2])	string_expression is returned from the numeric_expression1 starting location. Optionally, numeric_expression2 specifies the length of the substring.
TRIM([{LEADING | TRAILING | BOTH}] FROM string_expression)	When returned, either the leading or trailing spaces, or both, are trimmed from string_expression.

Conditions

A condition specifies a combination of one or more expressions and logical operators that evaluates to either TRUE, FALSE, or UNKNOWN. You can use a condition in the Where clause of the Delete, Select, and Update statements; and in the Having clauses of Select statements. Table 10-15 describes supported conditions.

 

Table 10-15. Conditions 

Condition	Description
Simple comparison	Specifies a comparison with expressions or subquery results. = , !=, <>, < , >, <=, <=
Group comparison	Specifies a comparison with any or all members in a list or subquery. [= , !=, <>, < , >, <=, <=] [ANY, ALL, SOME]
Membership	Tests for membership in a list or subquery. [NOT] IN
Range	Tests for inclusion in a range. [NOT] BETWEEN
NULL	Tests for nulls. IS NULL, IS NOT NULL
EXISTS	Tests for existence of rows in a subquery. [NOT] EXISTS
LIKE	Specifies a test involving pattern matching. [NOT] LIKE
Compound	Specifies a combination of other conditions. CONDITION [AND/OR] CONDITION