SQL Series...22

Value functions

A number of operations apply in a variety of contexts. Because you need to use these operations so often, incorporating them into SQL as value func-tions makes good sense. SQL offers relatively few value functions compared to PC database management systems such as Access or dBASE, but the few that SQL does have are probably the ones that you’ll use most often. SQL uses the following three types of value functions:

String value functions Numeric value functions Datetime value functions

String value functions

String value functions take one character string as an input and produce another character string as an output. SQL has six such functions:

SUBSTRING UPPER

LOWER TRIM

TRANSLATE CONVERT

SUBSTRING

Use the SUBSTRING function to extract a substring from a source string. The extracted substring is of the same type as the source string. If the source string is a CHARACTER VARYING string, for example, the substring is also a CHARACTER VARYING string. Following is the syntax of the SUBSTRING function:

SUBSTRING (string_value FROM start [FOR length])

The clause in square brackets ([ ]) is optional. The substring extracted from string_value begins with the character that start represents and contin-ues for length characters. If the FOR clause is absent, the substring extracted extends from the start character to the end of the string. Consider the fol-lowing example:

SUBSTRING (‘Bread, whole wheat’ FROM 8 FOR 7)

The substring extracted is ‘whole w’. This substring starts with the eighth character of the source string and has a length of seven characters. On the surface, SUBSTRING doesn’t seem like a very valuable function; if I have a lit-eral like ‘Bread, whole wheat’, I don’t need a function to figure out char-acters 8 through 14. SUBSTRING really is a valuable function, however, because the string value doesn’t need to be a literal. The value can be any expression that evaluates to a character string. Thus, I could have a variable named fooditem that takes on different values at different times. The follow-ing expression would extract the desired substring regardless of what charac-ter string the fooditem variable currently represents:

SUBSTRING (:fooditem FROM 8 FOR 7)

All the value functions are similar in that these functions can operate on expressions that evaluate to values as well as on the literal values themselves.

You need to watch out for a couple of things if you use the SUBSTRING func-tion. Make sure that the substring that you specify actually falls within the source string. If you ask for a substring starting at character eight but the source string is only four characters long, you get a null result. You must, therefore, have some idea of the form of your data before you specify a sub-string function. You also don’t want to specify a negative substring length, because the end of a string can’t precede the beginning.

If a column is of the VARCHAR type, you may not know how far the field extends for a particular row. This lack of knowledge doesn’t present a prob-lem for the SUBSTRING function. If the length that you specify goes beyond the right edge of the field, SUBSTRING returns whatever it finds. It doesn’t return an error.

Say that you have the following statement:

SELECT * FROM FOODS

WHERE SUBSTRING (Food FROM 8 FOR 7) = ‘white’ ;

This statement returns the row for white bread from the FOODS table, even though the value in the Food column (‘Bread, white’) is less than 14 char-acters long.

If any operand in the substring function has a null value, SUBSTRING returns a null result.

UPPER

The UPPER value function converts a character string to all uppercase charac-ters, as in the examples shown in the following table.

This Statement	Returns
UPPER (‘e. e. cummings’)	‘E. E. CUMMINGS’
UPPER (‘Isaac Newton, Ph.D’)	‘ISAAC NEWTON, PH.D.’

The UPPER function doesn’t affect a string that’s already in all uppercase characters.

LOWER

The LOWER value function converts a character string to all lowercase charac-ters, as in the examples in the following table.

This Statement Returns

LOWER (‘TAXES’) ‘taxes’

LOWER (‘E. E. Cummings’) ‘e. e. cummings’

The LOWER function doesn’t affect a string that’s already in all lowercase characters.

TRIM

 

Use the TRIM function to trim off leading or trailing blanks (or other charac-ters) from a character string. The following examples show how to use TRIM.

This Statement	Returns
TRIM (LEADING ‘ ‘ FROM ‘ treat ‘)	‘treat ‘
TRIM (TRAILING ‘ ‘ FROM ‘ treat ‘)	‘ treat’
TRIM (BOTH ‘ ‘ FROM ‘ treat ‘)	‘treat’
TRIM (BOTH ‘t’ from ‘treat’)	‘rea’

The default trim character is the blank, so the following syntax also is legal:

TRIM (BOTH FROM ‘ treat ‘)

This syntax gives you the same result as the third example in the table — ‘treat’.

TRANSLATE and CONVERT

The TRANSLATE and CONVERT functions take a source string in one character set and transform the original string into a string in another character set. Examples may be English to Kanji or Hebrew to French. The conversion func-tions that specify these transformations are implementation-specific. Consult the documentation of your implementation for details.

If translating from one language to another was as easy as invoking an SQL TRANSLATE function, that would be great. Unfortunately, the task is not that easy. All TRANSLATE does is translate a character in the first character set to

the corresponding character in the second character set. The function can, for example, translate ‘Ελλασ’ to ‘Ellas’. But it can’t translate ‘Ελλασ’ to

‘Greece’.

Numeric value functions

Numeric value functions can take a variety of data types as input, but the output is always a numeric value. SQL has 13 types of numeric value functions:

Position expression (POSITION) Extract expression (EXTRACT)

Length expression (CHAR_LENGTH, CHARACTER_LENGTH, OCTET_LENGTH)

Cardinality expression (CARDINALITY) Absolute value expression (ABS)

Modulus expression (MOD)

Natural logarithm (LN)

Exponential function (EXP) Power function (POWER) Square root (SQRT)

Floor function (FLOOR)

Ceiling function (CEIL, CEILING)

Width bucket function (WIDTH_BUCKET)

POSITION

POSITION searches for a specified target string within a specified source string and returns the character position where the target string begins. The syntax is as follows:

POSITION (target IN source)

The following table shows a few examples.

This Statement	Returns
POSITION (‘B’ IN ‘Bread, whole wheat’)	1
POSITION (‘Bre’ IN ‘Bread, whole wheat’)	1
POSITION (‘wh’ IN ‘Bread, whole wheat’)	8
POSITION (‘whi’ IN ‘Bread, whole wheat’)	0
POSITION (‘’ IN ‘Bread, whole wheat’)	1

If the function doesn’t find the target string, the POSITION function returns a zero value. If the target string has zero length (as in the last example), the POSITION function always returns a value of one. If any operand in the func-tion has a null value, the result is a null value.

EXTRACT

The EXTRACT function extracts a single field from a datetime or an interval.

The following statement, for example, returns 08:

EXTRACT (MONTH FROM DATE ‘2000-08-20’)

CHARACTER_LENGTH

The CHARACTER_LENGTH function returns the number of characters in a char-acter string. The following statement, for example, returns 16:

CHARACTER_LENGTH (‘Opossum, roasted’

As I note in regard to the SUBSTRING function, this function is not particularly useful if its argument is a literal like ‘Opossum, roasted’. I can just as easily write 16 as I can CHARACTER_LENGTH (‘Opossum, roasted’). In fact, writing 16 is easier. This function is more useful if its argument is an expression rather than a literal value.

OCTET_LENGTH

In music, a vocal ensemble made up of eight singers is called an octet. Typically, the parts that the ensemble represents are first and second soprano, first and second alto, first and second tenor, and first and second bass. In computer terminology, an ensemble of eight data bits is called a byte. The word byte is clever in that the term clearly relates to bit but implies something larger than a bit. A nice wordplay — but, unfortu-nately, nothing in the word byte conveys the concept of “eightness.” By borrowing the musical term, a more apt description of a collection of eight bits becomes possible.

Practically all modern computers use eight bits to represent a single alphanumeric character. More complex character sets (such as Chinese) require 16 bits to represent a single character. The OCTET_LENGTH function counts and returns the number of octets (bytes) in a string. If the string is a bit string, OCTET_LENGTH returns the number of octets you need to hold that number of bits. If the string is an English-language character string (with one octet per character), the function returns the number of characters in the string. If the string is a Chinese character string, the function returns a number that is twice the number of Chinese characters. The following string is an example:

OCTET_LENGTH (‘Beans, lima’)

This function returns 11, because each character takes up one octet.

Some character sets use a variable number of octets for different characters. In particular, some character sets that support mixtures of Kanji and Latin characters use escape characters to switch between the two character sets. A string that contains both Latin and Kanji may have, for example, 30 charac-ters and require 30 octets if all the characters are Latin; 62 characters if all the characters are Kanji (60 characters plus a leading and trailing shift char-acter); and 150 characters if the characters alternate between Latin and Kanji (because each Kanji character needs two octets for the character and one octet each for the leading and trailing shift characters). The OCTET_LENGTH function returns the number of octets you need for the current value of the string.

CARDINALITY

Cardinality deals with collections of elements such as arrays or multisets, where each element is a value of some data type. The cardinality of the collec-tion is the number of elements that it contains. One use of the CARDINALITY function might be:

CARDINALITY (TeamRoster)

This function would return 12, for example, if there were 12 team members on the roster. TeamRoster, a column in the TEAM table, can be either an array or a multiset. An array is an ordered collection of elements, and a multi-set is an unordered collection of elements. For a team roster, which changes frequently, multiset makes more sense.

ABS

The ABS function returns the absolute value of a numeric value expression.

ABS (-273)

This returns 273.

MOD

The MOD function returns the modulus of two numeric value expressions.

MOD (3,2)

This function returns 1, the modulus of three divided by two.

LN

The LN function returns the natural logarithm of a numeric value expression.

LN (9)

This function returns something like 2.197224577. The number of digits beyond the decimal point is implementation dependent.

EXP

This function raises the base of the natural logarithms e to the power speci-fied by a numeric value expression.

EXP (2)

 

This function returns something like 7.389056. The number of digits beyond the decimal point is implementation dependent.

POWER

This function raises the value of the first numeric value expression to the power of the second numeric value expression.

POWER (2,8)

This function returns 256, which is two raised to the eighth power.

SQRT

This function returns the square root of the value of the numeric value expression.

SQRT (4)

This function returns 2, the square root of four.

FLOOR

This function rounds the numeric value expression to the largest integer not greater than the expression.

FLOOR (3.141592)

This function returns 3.0.

CEIL or CEILING

This function rounds the numeric value expression to the smallest integer not less than the expression.

CEIL (3.141592)

This function returns 4.0.

WIDTH_BUCKET

The WIDTH_BUCKET function, used in online application processing (OLAP), is a function of four arguments, returning an integer between 0 (zero) and the value of the final argument plus 1 (one). It assigns the first argument to an equiwidth partitioning of the range of numbers between the second and third arguments. Values outside this range are assigned to either 0 (zero) or the value of the final argument plus 1 (one).

For example:

WIDTH_BUCKET ( PI, 0, 9, 5)

Suppose PI is a numeric value expression with a value of 3.141592. The exam-ple partitions the interval from zero to nine into five equal buckets, each with a width of two. The function returns a value of 2, because 3.141592 falls into the second bucket, which covers the range from two to four.

Datetime value functions

SQL includes three functions that return information about the current date, current time, or both. CURRENT_DATE returns the current date; CURRENT_TIME returns the current time; and CURRENT_TIMESTAMP returns (surprise!) both the current date and the current time. CURRENT_DATE doesn’t take an argu-ment, but CURRENT_TIME and CURRENT_TIMESTAMP both take a single argu-ment. The argument specifies the precision for the seconds part of the time value that the function returns. 

The following table offers some examples of these datetime value functions.

This Statement	Returns
CURRENT_DATE	2000-12-31
CURRENT_TIME (1)	08:36:57.3
CURRENT_TIMESTAMP (2)	2000-12-31	08:36:57.38

The date that CURRENT_DATE returns is DATE type data. The time that CURRENT_TIME (p) returns is TIME type data, and the timestamp that CURRENT_TIMESTAMP(p) returns is TIMESTAMP type data. Because SQL retrieves date and time information from your computer’s system clock, the information is correct for the time zone in which the computer resides.

In some applications, you may want to deal with dates, times, or timestamps as character strings to take advantage of the functions that operate on char-acter data. You can perform a type conversion by using the CAST expression.

Entering SQL statements into a Microsoft Access database

Access doesn’t make it easy to enter SQL state-ments. You have to enter all SQL statements as queries. Other products such as SQL Server, Oracle, MySQL, or PostgreSQL provide editors you can use to enter SQL statements. In SQL Server, you can use the Query Analyzer. For others, consult their documentation.You can enter SQL statements into Access, but the path to doing so is obscure.