Working with Null Values

A data field without a value in it is said to contain a null value. A null value is appropriate in two 10 situations:

where a value is unknown
where a value is not meaningful
However, a null value is not the same as zero. A null value is a value that is not there. Null values are normally displayed as blanks and zero values as numeric zeroes(0). Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators return null when given a null operand.

Nulls in SQL Functions All scalar functions (except NVL and TRANSLATE) return null when given a null argument. The NVL function can be used to return a value when a null occurs. For example, the expression NVL(COMM,O) returns 0 if COMM is null or the value of COMM if it is not null.

Most group functions ignore nulls. For example, consider a query that average the five values 1000, null,null,null and 2000. Such a query ignores the nulls and calculates the averages to be (1000+2000)/ 2 = 1500.

Nulls with Comparison Operators

To test for nulls, only use the comparison operators IS NULL and IS NOT NULL. If you use any other operator with nulls and the result depends on the value of the null, the result is unknown. Because null represents a lack of data, a null cannot be equal or unequal to any value or to another nun. However, note that ORACLE considers two nulls to be equal when evaluating a DECODE expression.

Nulls in Conditions

ORACLE treats conditions evaluating to unknown values as FALSE. For example, since the condition COMM= NULL is always unknown, a SELECT statement with this condition in it's WHERE clause returns no rows. Note that ORACLE returns no error message in this case .

CONVERSION FUNCTIONS

CHARTOROWID - CHARacter TO ROW Identifier. Changes a character string to act like an internal ORACLE row identifier, or RowID.

CONVERT - CONVERTs character string from one national languages set to another.

DECODE - DECODE's a CHAR, VARCHAR, or a NUMBER into any of several different character strings or NUMBER based on value.

HEXTORAW - HEXadecimal TO RAW. Changes a character string of hex numbers into binary.

RAWTOHEX - RAW TO HEXadecimal. Changes a string of binary numbers to a character string of hex numbers.

ROWIDTOCHAR - ROW Identifier TO CHARacter. Changes an internal Oracle row identifier, or RowID, to act like a character string.

TO CHAR - TO CHARacter. Converts a NUMBER or DATE so that it acts like a character string.

TO_DATE - Converts a NUMBER, CHAR, or VARCHAR to act like a DATE.

TO_MULTI BYTE - Converts the single byte characters in a character string to multibyte.

TO_NUMBER - Converts a CHAR or VARCHAR to act like a number

TO_SINGLE BYTE - Converts the multi byte characters in a CHAR or VARCHAR to
single bytes.

TRANSLATE - TRANSLATEs characters in a string into different characters.

Domain Name Search