常用Oracle函数(From OTN)

1.LPAD

Syntax

lpad::=

Text description of functions146.gif follows

Purpose

LPAD returns char1, left-padded to length n with the sequence of characters in char2; char2 defaults to a single blank. If char1 is longer than n, then this function returns the portion of char1 that fits in n.

Both char1 and char2 can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as char1.

The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string.

Examples

The following example left-pads a string with the characters "*" and ".":

SELECT LPAD('Page 1',15,'*.') "LPAD example"
   FROM DUAL;

LPAD example
---------------
*.*.*.*.*Page 1

2.TRANSLATE
Syntax
translate::=
 
Text description of translate

Purpose
TRANSLATE returns char with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in char that are not in from_string are not replaced. The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in char, then they are removed from the return value.
You cannot use an empty string for to_string to remove all characters in from_string from the return value. Oracle interprets the empty string as null, and if this function has a null argument, then it returns null.
Note: This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion. Please refer to "Datatype Comparison Rules" for more information.
Examples
The following statement translates a license number. All letters 'ABC...Z' are translated to 'X' and all digits '012 . . . 9' are translated to '9':
SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', 
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
     FROM DUAL;
 
License
--------
9XXX999 

The following statement returns a license number with the characters removed and the digits remaining:
SELECT TRANSLATE('2KRW229',
   '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789') 
   "Translate example"
     FROM DUAL;
 
Translate example
-----------------
2229
 
3.SOUNDEX
Syntax
soundex::=
 
Text description of soundex

Purpose
SOUNDEX returns a character string containing the phonetic representation of char. This function lets you compare words that are spelled differently, but sound alike in English.
The phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:
Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.
Assign numbers to the remaining letters (after the first) as follows: b, f, p, v = 1
c, g, j, k, q, s, x, z = 2
d, t = 3
l = 4
m, n = 5
r = 6
If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then omit all but the first.
Return the first four bytes padded with 0.
char can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The return value is the same datatype as char.
Note: This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion. Please refer to "Datatype Comparison Rules" for more information.
Examples
The following example returns the employees whose last names are a phonetic representation of "Smyth":
SELECT last_name, first_name
     FROM hr.employees
     WHERE SOUNDEX(last_name)
         = SOUNDEX('SMYTHE');

LAST_NAME  FIRST_NAME
---------- ----------
Smith      Lindsey
Smith      William

4.TRIM
Syntax
trim::=
 
Text description of trim

Purpose
TRIM enables you to trim leading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, then you must enclose it in single quotes.
If you specify LEADING, then Oracle removes any leading characters equal to trim_character.
If you specify TRAILING, then Oracle removes any trailing characters equal to trim_character.
If you specify BOTH or none of the three, then Oracle removes leading and trailing characters equal to trim_character.
If you do not specify trim_character, then the default value is a blank space.
If you specify only trim_source, then Oracle removes leading and trailing blank spaces.
The function returns a value with datatype VARCHAR2. The maximum length of the value is the length of trim_source.
If either trim_source or trim_character is null, then the TRIM function returns null.
Both trim_character and trim_source can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as trim_source.
Examples
This example trims leading and trailing zeroes from a number:
SELECT TRIM (0 FROM 0009872348900) "TRIM Example"
   FROM DUAL;

TRIM Example
------------
    98723489