3.3.4 String Functions and Operators

Table of Contents Previous Next


3 Enhanced SQL Features : 3.3 Extended Functions and Operators : 3.3.4 String Functions and Operators

This section describes functions and operators for examining and manipulating string values. Strings in this context include values of the types CHAR, VARCHAR2, and CLOB. Unless otherwise noted, all of the functions listed below work on all of these types, but be wary of potential effects of automatic padding when using the CHAR type. Generally, the functions described here also work on data of non-string types by converting that data to a string representation first.
string || string
CONCAT(string, string)
HEXTORAW(varchar2)
Converts a VARCHAR2 value to a RAW value
Converts a RAW value to a HEXADECIMAL value
INSTR(string, set, [ start [, occurrence ] ])
Finds the location of a set of characters in a string, starting at position start in the string, string, and looking for the first, second, third and so on occurrences of the set. Returns 0 if the set is not found.
INSTRB(string, set)
Returns the position of the set within the string. Returns 0 if set is not found.
INSTRB(string, set, start)
Returns the position of the set within the string, beginning at start. Returns 0 if set is not found.
INSTRB(string, set, start, occurrence)
Returns the position of the specified occurrence of set within the string, beginning at start. Returns 0 if set is not found.
LOWER(string)
Convert string to lower case
SUBSTR(string, start [, count ])
Extract substring starting from start and going for count characters. If count is not specified, the string is clipped from the start till the end.
SUBSTRB(string, start [, count ])
Same as SUBSTR except startcount are in number of bytes.
SUBSTRB('abc',3) (assuming a double-byte character set)
SUBSTR2(string, start [, count ])
Alias for SUBSTR.
SUBSTR2(string, start [, count ])
Alias for SUBSTRB.
SUBSTR2('abc',3) (assuming a double-byte character set)
SUBSTR4(string, start [, count ])
Alias for SUBSTR.
SUBSTR4(string, start [, count ])
Alias for SUBSTRB.
SUBSTR4('abc',3) (assuming a double-byte character set)
SUBSTRC(string, start [, count ])
Alias for SUBSTR.
SUBSTRC(string, start [, count ])
Alias for SUBSTRB.
SUBSTRC('abc',3) (assuming a double-byte character set)
TRIM([ LEADING | TRAILING | BOTH ] [ characters ] FROM string)
LTRIM(string [, set])
Removes all the characters specified in set from the left of a given string. If set is not specified, a blank space is used as default.
RTRIM(string [, set])
Removes all the characters specified in set from the right of a given string. If set is not specified, a blank space is used as default.
UPPER(string)
Convert string to upper case
ASCII(string)
DECODE(expr, expr1a, expr1b [, expr2a, expr2b ]... [, default ])
Same as argument types of expr1b, expr2b,..., default
Finds first match of expr with expr1a, expr2a, etc. When match found, returns corresponding parameter pair, expr1b, expr2b, etc. If no match found, returns default. If no match found and default not specified, returns null.
INITCAP(string)
LENGTH('Côte d''Azur')
This function is identical in functionality to LENGTH; the function name is supported for compatibility.
LENGTHC('Côte d''Azur')
This function is identical in functionality to LENGTH; the function name is supported for compatibility.
LENGTH2('Côte d''Azur')
This function is identical in functionality to LENGTH; the function name is supported for compatibility.
LENGTH4('Côte d''Azur')
LENGTHB('Côte d''Azur')
LPAD(string, length INTEGER [, fill ])
Fill up string to size, length by prepending the characters, fill (a space by default). If string is already longer than length then it is truncated (on the right).
REPLACE(string, search_string [, replace_string ]
Replaces one value in a string with another. If you do not specify a value for replace_string, the search_string value when found, is removed.
RPAD(string, length INTEGER [, fill ])
Fill up string to size, length by appending the characters, fill (a space by default). If string is already longer than length then it is truncated.
TRANSLATE(string, from, to)
Any character in string that matches a character in the from set is replaced by the corresponding character in the to set.

3 Enhanced SQL Features : 3.3 Extended Functions and Operators : 3.3.4 String Functions and Operators

Table of Contents Previous Next