Oracle Character Functions

Character Functions Returning Character Values

Character functions that return character values return values of the same datatype as the input argument.

• Functions that return CHAR values are limited in length to 2000 bytes.
• Functions that return VARCHAR2 values are limited in length to 4000 bytes.

For both of these types of functions, if the length of the return value exceeds the limit, then Oracle truncates it and returns the result without an error message.

• Functions that return CLOB values are limited to 4 GB.

For CLOB functions, if the length of the return values exceeds the limit, then Oracle raises an error and returns no data.

The character functions that return character values are:

CHR

CONCAT

INITCAP

LOWER

LPAD

LTRIM

NLS_INITCAP

NLS_LOWER

NLSSORT

NLS_UPPER

REPLACE

RPAD

RTRIM

SOUNDEX

SUBSTR

TRANSLATE

TREAT

TRIM

UPPER

 

CONCAT

Syntax

concat::=

CONCAT(char1,char2)

Purpose

CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is in the same character set as char1. Its datatype depends on the datatypes of the arguments.

In concatenations of two different datatypes, Oracle returns the datatype that results in a lossless conversion. Therefore, if one of the arguments is a LOB, then the returned value is a LOB. If one of the arguments is a national datatype, then the returned value is a national datatype. For example:

• CONCAT(CLOB, NCLOB) returns NCLOB
• CONCAT(NCLOB, NCHAR) returns NCLOB
• CONCAT(NCLOB, CHAR) returns NCLOB
• CONCAT(NCHAR, CLOB) returns NCLOB

This function is equivalent to the concatenation operator (||).

See Also: "Concatenation Operator" for information on the CONCAT operator

Examples

This example uses nesting to concatenate three character strings:

SELECT CONCAT(CONCAT(last_name, '''s job category is '),

      job_id) "Job" 

   FROM employees 

   WHERE employee_id = 152;

Job

------------------------------------------------------

Hall's job category is SA_REP

 

INITCAP

Syntax

initcap::=

INITCAP ( char )

Purpose

INITCAP 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.

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 capitalizes each word in the string:

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

Capitals

---------

The Soap

 

LOWER

Syntax

lower::=

LOWER ( char )

Purpose

LOWER returns char, with all letters lowercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as char.

Examples

The following example returns a string in lowercase:

SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase"

   FROM DUAL;

Lowercase

--------------------

mr. scott mcmillan

 

LPAD

Syntax

lpad::=

LPAD ( char1 , n [, char2] )

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

 

LTRIM

Syntax

ltrim::=

LTRIM ( char [, set] )

Purpose

LTRIM removes characters from the left of char, with all the leftmost characters that appear in set removed; set defaults to a single blank. If char is a character literal, then you must enclose it in single quotes. 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.

Both char and set 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 char.

Examples

The following example trims all of the left-most x's and y's from a string:

SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example"

   FROM DUAL;

LTRIM example

------------

XxyLAST WORD

 

REPLACE

Syntax

replace::=

REPLACE ( char , search_string [, replacement_string] )

Purpose

REPLACE returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed. If search_string is null, then char is returned.

Both search_string and replacement_string, as well as char, 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 char.

This function provides functionality related to that provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings.

See Also: TRANSLATE

Examples

The following example replaces occurrences of "J" with "BL":

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

     FROM DUAL;

Changes

--------------

BLACK and BLUE

 

RPAD

Syntax

rpad::=

RPAD ( char1 , n [, char2] )

Purpose

RPAD 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, 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 right-pads a name with the letters "ab" until it is 12 characters long:

SELECT RPAD('MORRISON',12,'ab') "RPAD example"

     FROM DUAL;

RPAD example

-----------------

MORRISONabab

 

RTRIM

Syntax

rtrim::=

RTRIM ( char [, set ] )

Purpose

RTRIM returns char, with all the rightmost characters that appear in set removed; set defaults to a single blank. If char is a character literal, then you must enclose it in single quotes. RTRIM works similarly to LTRIM.

Both char and set 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 char.

Examples

The following example trims the letters "xy" from the right side of a string:

SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM example"

     FROM DUAL;

RTRIM examp

-----------

BROWNINGyxX

 

SUBSTR

Syntax

substr::=

{ SUBSTR

| SUBSTRB

| SUBSTRC

| SUBSTR2

| SUBSTR4

}

( string , position [, substring_length] )

Purpose

The "substring" functions return a portion of string, beginning at character position, substring_length characters long. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters. SUBSTR2 uses UCS2 codepoints. SUBSTR4 uses UCS4 codepoints.

• If position is 0, then it is treated as 1.
• If position is positive, then Oracle counts from the beginning of string to find the first character.
• If position is negative, then Oracle counts backward from the end of string.
• If substring_length is omitted, then Oracle returns all characters to the end of string. If substring_length is less than 1, then a null is returned.

string can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as string. Floating-point numbers passed as arguments to SUBSTR are automatically converted to integers.

Examples

The following example returns several specified substrings of "ABCDEFG":

SELECT SUBSTR('ABCDEFG',3,4) "Substring"

     FROM DUAL;

Substring

---------

CDEF

SELECT SUBSTR('ABCDEFG',-5,4) "Substring"

     FROM DUAL;

Substring

---------

CDEF

Assume a double-byte database character set:

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

     FROM DUAL;

Substring with bytes

--------------------

CD

 

TRANSLATE

Syntax

translate::=

TRANSLATE ( 'char' , 'from_string' , 'to_string' )

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

 

TRIM

Syntax

trim::=

TRIM 

( [{ { LEADING | TRAILING | BOTH } [trim_character])

   | trim_character

   }

   FROM 

  ]

  trim_source 

)

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

 

UPPER

Syntax

upper::=

UPPER ( char )

Purpose

UPPER returns char, with all letters uppercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as char.

Examples

The following example returns a string in uppercase:

SELECT UPPER('Large') "Uppercase"

   FROM DUAL;

Upper

-----

LARGE

 

Character Functions Returning Number Values

Character functions that return number values can take as their argument any character datatype.

The character functions that return number values are:

ASCII

INSTR

LENGTH

 

ASCII

Syntax

ascii::=

ASCII(char)

Purpose

ASCII returns the decimal representation in the database character set of the first character of char.

char can be of datatype CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The value returned is of datatype NUMBER. If your database character set is 7-bit ASCII, then this function returns an ASCII value. If your database character set is EBCDIC Code, then this function returns an EBCDIC value. There is no corresponding EBCDIC character function.

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 ASCII decimal equivalent of the letter Q:

SELECT ASCII('Q') FROM DUAL;

ASCII('Q')

----------

        81

 

INSTR

Syntax

instr::=

{ INSTR | INSTRB | INSTRC | INSTR2 | INSTR4 }

( string , substring [, position [, occurrence]] )

Purpose

The "in string" functions search string for substring. The function returns an integer indicating the position of the character in string that is the first character of this occurrence. INSTR calculates strings using characters as defined by the input character set. INSTRB uses bytes instead of characters. INSTRC uses Unicode complete characters. INSTR2 uses UCS2 codepoints. INSTR4 uses UCS4 codepoints.

• position is an nonzero integer indicating the character of string where Oracle begins the search. If position is negative, then Oracle counts and searches backward from the end of string.
• occurrence is an integer indicating which occurrence of string Oracle should search for. The value of occurrence must be positive.

Both string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The value returned is of NUMBER datatype.

The default values of both position and occurrence are 1, meaning Oracle begins searching at the first character of string for the first occurrence of substring. The return value is relative to the beginning of string, regardless of the value of position, and is expressed in characters. If the search is unsuccessful (if substring does not appear occurrence times after the position character of string), then the return value is 0.

Examples

The following example searches the string "CORPORATE FLOOR", beginning with the third character, for the string "OR". It returns the position in CORPORATE FLOOR at which the second occurrence of "OR" begins:

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)

  "Instring" FROM DUAL;

  Instring

----------

        14

In the next example, Oracle counts backward from the last character to the third character from the end, which is the first "O" in "FLOOR". Oracle then searches backward for the second occurrence of OR, and finds that this second occurrence begins with the second character in the search string :

SELECT INSTR('CORPORATE FLOOR','OR', -3, 2)

"Reversed Instring"

     FROM DUAL;

Reversed Instring

-----------------

               2

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

 

LENGTH

Syntax

length::=

{ LENGTH | LENGTHB | LENGTHC | LENGTH2 | LENGTH4 } ( char )

Purpose

The "length" functions return the length of char. LENGTH calculates length using characters as defined by the input character set. LENGTHB uses bytes instead of characters. LENGTHC uses Unicode complete characters. LENGTH2 uses UCS2 codepoints. LENGTH4 uses UCS4 codepoints.

char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is of datatype NUMBER. If char has datatype CHAR, then the length includes all trailing blanks. If char is null, then this function returns null.

Examples

The following example uses the LENGTH function using a single-byte database character set.

SELECT LENGTH('CANDIDE') "Length in characters"

   FROM DUAL;

Length in characters

--------------------

                   7

This example assumes a double-byte database character set.

SELECT LENGTHB ('CANDIDE') "Length in bytes"

   FROM DUAL;

Length in bytes

---------------

             14