Xem mẫu
Common Oracle Functions: A Function Review
“is”, then the “is” in “This” will be replaced along with the word “is”, as shown by the following query:
SELECT REPLACE (`This is a test`,`is`,` may be `) FROM dual
This would give:
REPLACE(`THISISATEST`,`IS` --------------------------Th may be may be a test
If the look for string is not present, then the replacing does not occur, as shown by the following query:
SELECT REPLACE (`This is a test`,`glurg`,` may be `) FROM dual
Which would give:
REPLACE(`THISI --------------This is a test
The TRIM Function
TRIM is a function that removes characters from the left or right ends of a string or both ends. The TRIM function was added in Oracle 9. Originally, LTRIM and RTRIM were used for trimming characters from the left or right ends of strings. TRIM supercedes both of these.
The general syntax of TRIM is:
TRIM ([where] [trim character] FROM subject string)
The optional where is one of the keywords “leading,” “trailing,” or “both.”
24
Chapter | 1
If the optional trim character is not present, then blanks will be trimmed. Trim character may be any character. The word FROM is necessary only if where or trim character is present. Here is an example:
SELECT TRIM (` spaces
FROM dual
This string has leading and trailing
`)
Which gives:
TRIM(`THISSTRINGHASLEADINGANDTRAILINGSPACES -------------------------------------------This string has leading and trailing spaces
Both the leading and trailing spaces are deleted. This is probably the most common use of the function. We can be more explicit in the use of the function, as shown in the following query:
SELECT TRIM (both ` ` from ` String with blanks `) FROM dual
Which gives:
TRIM(BOTH``FROM`ST ------------------String with blanks
In these examples, characters rather than spaces are trimmed:
SELECT TRIM(`F` from `Frogs prefer deep water`) FROM dual
Which would give:
TRIM(`F`FROM`FROGSPREF ----------------------rogs prefer deep water
25
Common Oracle Functions: A Function Review
Here are some other examples.
Example 1:
SELECT TRIM(leading `F` from `Frogs prefer deep water`) FROM dual
Which would give:
TRIM(LEADING`F`FROM`FR ----------------------rogs prefer deep water
Example 2:
SELECT TRIM(trailing `r` from `Frogs prefer deep water`) FROM dual
Which would give:
TRIM(TRAILING`R`FROM`F ----------------------Frogs prefer deep wate
Example 3:
SELECT TRIM (both `z` from `zzzzz I am asleep zzzzzz`) FROM dual
Which would give:
TRIM(BOTH`Z`F -------------I am asleep
In the last example, note that the blank space was pre-served because it was not trimmed. To get rid of the leading/trailing blank(s) we can nest TRIMs like this:
SELECT TRIM(TRIM (both `z` from `zzzzz I am asleep zzzzzz`)) FROM dual
26
Chapter | 1
This would give:
TRIM(TRIM(B -----------I am asleep
Date Functions
Oracle’s date functions allow one to manage and handle dates in a far easier manner than if one had to actually create calendar tables or use complex algorithms for date calculations. First we must note that the date data type is not a character format. Columns with date data types contain both date and time. We must format dates to see all of the information contained in a date. If you type:
SELECT SYSDATE FROM dual
You will get:
SYSDATE
---------10-SEP-06
The format of the TO_CHAR function (i.e., convert to a character string) is full of possibilities. (TO_CHAR is covered in more detail in Chapter 2.) Here is an example:
SELECT TO_CHAR(SYSDATE, `dd Mon, yyyy hh24:mi:ss`) FROM dual
27
Common Oracle Functions: A Function Review
This gives:
TO_CHAR(SYSDATE,`DDMO ---------------------10 Sep, 2006 14:04:59
This presentation gives us not only the date in “dd Mon yyyy” format, but also gives us the time in 24-hour hours, minutes, and seconds.
We can add months to any date with the ADD_ MONTHS function like this:
SELECT TO_CHAR(SYSDATE, `ddMONyyyy`) Today, TO_CHAR(ADD_MONTHS(SYSDATE, 3), `ddMONyyyy`) "+ 3 mon", TO_CHAR(ADD_MONTHS(SYSDATE, -23), `ddMONyyyy`) "- 23 mon" FROM dual
This will give us:
TODAY + 3 mon - 23 mon --------- --------- ---------10SEP2006 10DEC2006 10OCT2004
In this example, note that the ADD_MONTHS func-tion is applied to SYSDATE, a date data type, and then the result is converted to a character string with TO_CHAR.
The LAST_DAY function returns the last day of any month, as shown in the following query:
SELECT TO_CHAR(LAST_DAY(`23SEP2006`)) FROM dual
This gives us:
TO_CHAR(L ---------30-SEP-06
28
...
- tailieumienphi.vn
nguon tai.lieu . vn