Syntax  

CHR(n [USING NCHAR_CS])

Purpose  

Returns the character having the binary equivalent to n in either the database character set or the national character set.

If the USING NCHAR_CS clause is not specified, this function returns the character having the binary equivalent to n as a VARCHAR2 value in the database character set.

If the USING NCHAR_CS clause is specified, this function returns the character having the binary equivalent to n as a NVARCHAR2 value in the national character set.

Example 1  

SELECT CHR(67)||CHR(65)||CHR(84) "Dog"
  FROM DUAL;
Dog
---
CAT

Example 2  

SELECT CHR(16705 USING NCHAR_CS) FROM DUAL;
 
C
-
A

Syntax  

CONCAT(char1, char2)

Purpose  

Returns char1 concatenated with char2. This function is equivalent to the concatenation operator (||). For information on this operator, see "Concatenation Operator".

Example  

This example uses nesting to concatenate three character strings:

SELECT CONCAT( CONCAT(ename, ' is a '), job) "Job"
FROM emp
WHERE empno = 7900;

Job
-----------------
JAMES is a CLERK

Purpose  

Returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

Example  

SELECT INITCAP('the soap') "Capitals" FROM DUAL;

Capitals
---------
The Soap

Purpose  

Returns char, with all letters lowercase. The return value has the same datatype as the argument char (CHAR or VARCHAR2).

Example  

SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase"
   FROM DUAL;

Lowercase
--------------------
mr. scott mcmillan 

Purpose  

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, this function returns the portion of char1 that fits in n.

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.

Example  

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

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

Syntax  

LTRIM(char [,set])

Purpose  

Removes characters from the left of char, with all the leftmost characters that appear in set removed; set defaults to a single blank. Oracle begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result.

Example  

SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example"
     FROM DUAL;

LTRIM exampl
------------
XxyLAST WORD

Purpose  

Returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric. The value of 'nlsparams' can have this form:

'NLS_SORT = sort'

where sort is either a linguistic sort sequence or BINARY. The linguistic sort sequence handles special linguistic requirements for case conversions. Note that these requirements can result in a return value of a different length than the char. If you omit 'nlsparams', this function uses the default sort sequence for your session. For information on sort sequences, see Oracle8 Reference.

Example  

SELECT NLS_INITCAP
   ('ijsland', 'NLS_SORT = XDutch') "Capitalized"
   FROM DUAL;

Capital
-------
IJsland

Syntax  

NLS_LOWER(char [, 'nlsparams'] )

Purpose  

Returns char, with all letters lowercase. The 'nlsparams' can have the same form and serve the same purpose as in the NLS_INITCAP function.

Example  

SELECT NLS_LOWER
   ('CITTA''', 'NLS_SORT = XGerman') "Lowercase"
   FROM DUAL;

Lower
-----
cittá

Syntax  

NLS_UPPER(char [, 'nlsparams'] )

Purpose  

Returns char, with all letters uppercase. The 'nlsparams' can have the same form and serve the same purpose as in the NLS_INITCAP function.

Example  

SELECT NLS_UPPER
   ('groe', 'NLS_SORT = XGerman') "Uppercase"
     FROM DUAL;

Upper
-----
GROSS 

Syntax  

REPLACE(char,search_string[,replacement_string])

Purpose  

Returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, all occurrences of search_string are removed. If search_string is null, char is returned. This function provides a superset of the functionality provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE allows you to substitute one string for another as well as to remove character strings.

Example  

SELECT REPLACE('JACK and JUE','J','BL') "Changes"
     FROM DUAL;

Changes
--------------
BLACK and BLUE

Syntax  

RPAD(char1, n [,char2])

Purpose  

Returns char1, right-padded to length n with char2, replicated as many times as necessary; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.

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.

Example  

SELECT RPAD('MORRISON',12,'ab') "RPAD example"
     FROM DUAL;

RPAD example
-----------------
MORRISONabab

Syntax  

RTRIM(char [,set]

Purpose  

Returns char, with all the rightmost characters that appear in set removed; set defaults to a single blank. RTRIM works similarly to LTRIM.

Example  

SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g."
     FROM DUAL;
 
RTRIM e.g
-------------
BROWNINGyxX

Syntax  

SOUNDEX(char)

Purpose  

Returns a character string containing the phonetic representation of char. This function allows you to 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 assigned number are adjacent, remove all but the first.

• Return the first four bytes padded with 0.

Example  

SELECT ename
     FROM emp
     WHERE SOUNDEX(ename)
         = SOUNDEX('SMYTHE');
 
ENAME
----------
SMITH

Syntax  

SUBSTR(char, m [,n])

Purpose  

Returns a portion of char, beginning at character m, n characters long. If m is 0, it is treated as 1. If m is positive, Oracle counts from the beginning of char to find the first character. If m is negative, Oracle counts backwards from the end of char. If n is omitted, Oracle returns all characters to the end of char. If n is less than 1, a null is returned.

Floating-point numbers passed as arguments to substr are automatically converted to integers.

Example 1  

SELECT SUBSTR('ABCDEFG',3.1,4) "Subs"
     FROM DUAL;
 
Subs
----
CDEF

Example 2  

SELECT SUBSTR('ABCDEFG',-5,4) "Subs"
     FROM DUAL;

Subs
----
CDEF

Syntax  

SUBSTR(char, m [,n])

Purpose  

The same as SUBSTR, except that the arguments m and n are expressed in bytes, rather than in characters. For a single-byte database character set, SUBSTRB is equivalent to SUBSTR.

Floating-point numbers passed as arguments to substrb are automatically converted to integers.

Example  

Assume a double-byte database character set:

SELECT SUBSTRB('ABCDEFG',5,4.2)
     "Substring with bytes"
     FROM DUAL;

Substring with bytes
--------------------
CD

Syntax  

TRANSLATE(char, from, to)

Purpose  

Returns char with all occurrences of each character in from replaced by its corresponding character in to. Characters in char that are not in from are not replaced. The argument from can contain more characters than to. In this case, the extra characters at the end of from have no corresponding characters in to. If these extra characters appear in char, they are removed from the return value. You cannot use an empty string for to to remove all characters in from from the return value. Oracle interprets the empty string as null, and if this function has a null argument, it returns null.

Example 1  

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 

Example 2  

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

Syntax  

UPPER(char)

Purpose  

Returns char, with all letters uppercase. The return value has the same datatype as the argument char.

Example  

SELECT UPPER('Large') "Uppercase"
     FROM DUAL;

Upper
-----
LARGE

Syntax  

ASCII(char)

Purpose  

Returns the decimal representation in the database character set of the first character of char. If your database character set is 7-bit ASCII, this function returns an ASCII value. If your database character set is EBCDIC Code Page 500, this function returns an EBCDIC value. Note that there is no similar EBCDIC character function.

Example  

SELECT ASCII('Q') 
     FROM DUAL;
 
ASCII('Q')
----------
        81

Syntax  

INSTR (char1,char2 [,n[,m]])

Purpose  

Searches char1 beginning with its nth character for the mth occurrence of char2 and returns the position of the character in char1 that is the first character of this occurrence. If n is negative, Oracle counts and searches backward from the end of char1. The value of m must be positive. The default values of both n and m are 1, meaning Oracle begins searching at the first character of char1 for the first occurrence of char2. The return value is relative to the beginning of char1, regardless of the value of n, and is expressed in characters. If the search is unsuccessful (if char2 does not appear m times after the nth character of char1) the return value is 0.

Example 1  

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)
  "Instring" FROM DUAL;
 
  Instring
----------
        14

Example 2  

SELECT INSTR('CORPORATE FLOOR','OR', -3, 2)
"Reversed Instring"
     FROM DUAL;
 
Reversed Instring
-----------------
                2

Syntax  

INSTRB(char1,char2[,n[,m]])

Purpose  

The same as INSTR, except that n and the return value are expressed in bytes, rather than in characters. For a single-byte database character set, INSTRB is equivalent to INSTR.

Example  

This example assumes a double-byte database character set.

SELECT INSTRB('CORPORATE FLOOR','OR',5,2)
"Instring in bytes"
FROM DUAL;

Instring in bytes
-----------------
               27

Syntax  

LENGTH(char)

Purpose  

Returns the length of char in characters. If char has datatype CHAR, the length includes all trailing blanks. If char is null, this function returns null.

Example  

SELECT LENGTH('CANDIDE') "Length in characters"
FROM DUAL;
 
Length in characters
--------------------
                   7

Syntax  

LENGTHB(char)

Purpose  

Returns the length of char in bytes. If char is null, this function returns null. For a single-byte database character set, LENGTHB is equivalent to LENGTH.

Example  

This example assumes a double-byte database character set.

SELECT LENGTHB ('CANDIDE') "Length in bytes"
     FROM DUAL;
 
Length in bytes
---------------
             14

Syntax  

NLSSORT(char [, 'nlsparams'])

Purpose  

Returns the string of bytes used to sort char. The value of 'nlsparams' can have the form

'NLS_SORT = sort'

where sort is a linguistic sort sequence or BINARY. If you omit 'nlsparams', this function uses the default sort sequence for your session. If you specify BINARY, this function returns char. For information on sort sequences, see the discussions of national language support in Oracle8 Reference.  

Example  

This function can be used to specify comparisons based on a linguistic sort sequence rather on the binary value of a string:

SELECT ename FROM emp
   WHERE NLSSORT (ename, 'NLS_SORT = German')
   > NLSSORT ('S', 'NLS_SORT = German') ORDER BY ename;
 
ENAME
----------
SCOTT
SMITH
TURNER
WARD