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 RAWTOHEX(raw) 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 start and count are in number of bytes. SUBSTRB('abc',3) (assuming a double-byte character set) SUBSTR2(string, start [, count ]) SUBSTR2(string, start [, count ]) SUBSTR2('abc',3) (assuming a double-byte character set) SUBSTR4(string, start [, count ]) SUBSTR4(string, start [, count ]) SUBSTR4('abc',3) (assuming a double-byte character set) SUBSTRC(string, start [, count ]) SUBSTRC(string, start [, count ]) 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 Additional string manipulation functions are available and are listed in the following table. Some of them are used internally to implement the SQL-standard string functions listed in Table 2‑17.
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') LENGTHC('Côte d''Azur') LENGTH2('Côte d''Azur') 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. Note: This section describes a functionality that is not compatible with Oracle databases, which may lead to some inconsistency when converting data from Oracle to Advanced Server.For Advanced Server, when a column value is NULL, the concatenation of the column with a text string may result in either of the following:The result is dependent upon the data type of the NULL column and the way in which the concatenation is done.If one uses the string concatenation operator ‘||’, then the types that have implicit coercion to text as listed in Table 2‑19 will not truncate the string if one of the input parameters is NULL, whereas for other types it will truncate the string unless the explicit type cast is used (that is, ::text). Also, to see the consistent behavior in the presence of nulls, one can use the CONCAT function.
For information on the column output, see the pg_cast system catalog in the PostgreSQL core documentation available at:So for example, data type UUID is not in this list and therefore does not have the implicit coercion to text. As a result, certain concatenation attempts with a NULL UUID column results in a truncated text result.The following table is created for this example with a single row with all NULL column values.Columns boolean_type and char_type have the implicit coercion to text while column uuid_type does not.Thus, string concatenation with the concatenation operator ‘||‘ against columns boolean_type or char_type results in the following:But concatenation with column uuid_type results in the loss of the x= string:However, using explicit casting with ::text prevents the loss of the x= string:Using the CONCAT function also preserves the x= string:Thus, depending upon the data type of a NULL column, explicit casting or the CONCAT function should be used to avoid loss of some text string.18.104.22.168 SYS_GUIDThe SYS_GUID function generates and returns a globally unique identifier; the identifier takes the form of 16 bytes of RAW data. The SYS_GUID function is based on the uuid-ossp module to generate universally unique identifiers. The synopsis is:The following example adds a column to the table EMP, inserts a unique identifier, and returns a 16-byte RAW value: