1.LPAD
Syntax
lpad::=
Text description of lpad
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 |
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
Text description of soundex
Purpose
SOUNDEX
returns a character string containing the phonetic representation ofchar
. 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 |
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 totrim_character
. - If you specify
TRAILING
, then Oracle removes any trailing characters equal totrim_character
. - If you specify
BOTH
or none of the three, then Oracle removes leading and trailing characters equal totrim_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 oftrim_source
. - If either
trim_source
ortrim_character
is null, then theTRIM
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