Oracle DBA FAQ - Understanding SQL Basics

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6

(Continued from previous part...)

How To Convert Characters to Times?

You can convert dates to characters using the TO_CHAR() function as shown in the following examples:

SELECT TO_CHAR(TO_DATE('04:49:49', 'HH:MI:SS'), 
  'DD-MON-YYYY HH24:MI:SS') FROM DUAL;
  -- Default date is the first day of the current month
01-MAY-2006 04:49:49

SELECT TO_CHAR(TO_TIMESTAMP('16:52:57.847000000', 
  'HH24:MI:SS.FF9'), 'DD-MON-YYYY HH24:MI:SS.FF9') 
  FROM DUAL;
01-MAY-2006 16:52:57.847000000

SELECT TO_CHAR(TO_DATE('69520', 'SSSSS'), 
  'DD-MON-YYYY HH24:MI:SS') FROM DUAL;
01-MAY-2006 19:18:40

What Is NULL?

NULL is a special value representing "no value" in all data types. NULL can be used on in operations like other values. But most opertations has special rules when NULL is involved. The tutorial exercise below shows you some examples:

SET NULL 'NULL'; -- Make sure NULL is displayed

SELECT NULL FROM DUAL;
N
-
N
U
L
L

SELECT NULL + NULL FROM DUAL;
 NULL+NULL
----------
NULL
  
SELECT NULL + 7 FROM DUAL;
    NULL+7
----------
NULL

SELECT NULL * 7 FROM DUAL;
    NULL*7
----------
NULL


SELECT NULL || 'A' FROM DUAL;
N
-
A

SELECT NULL + SYSDATE FROM DUAL;
NULL+SYSD
---------
NULL

How To Use NULL as Conditions?

If you want to compare values against NULL as conditions, you should use the "IS NULL" or "IS NOT NULL" operator. Do not use "=" or "<>" against NULL. The sample script below shows you some good examples:

SELECT 'A' IS NULL FROM DUAL;
  -- Error: Boolean is not data type. 
  -- Boolean can only be used as conditions
  
SELECT CASE WHEN 'A' IS NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
FALSE

SELECT CASE WHEN '' IS NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
TRUE

SELECT CASE WHEN 0 IS NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
FALSE

SELECT CASE WHEN NULL IS NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
TRUE

SELECT CASE WHEN 'A' = NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
  -- Do not use "="
FALSE

SELECT CASE WHEN 'A' <> NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
  -- Do not use "<>"
FALSE

SELECT CASE WHEN NULL = NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
  -- Do not use "="
FALSE

(Continued on next part...)

Part: 1 2 3 4 5 6

More...