String functions and operators v16

These functions and operators are for examining and manipulating string values. Strings in this context include values of the types CHAR, VARCHAR2, and CLOB. Unless otherwise noted, all of these functions work on all of these types, but be aware of potential effects of automatic padding when using the CHAR type. Generally, the functions described here also work on data of nonstring types by converting that data to a string representation first.

Overview of string functions

FunctionReturn typeDescriptionExampleResult
string || stringCLOBString concatenation.'Enterprise' || 'DB'EnterpriseDB
CONCAT(string, string)CLOBString concatenation.'a' || 'b'ab
HEXTORAW(varchar2)RAWConverts a VARCHAR2 value to a RAW value.HEXTORAW('303132')'012'
RAWTOHEX(raw)VARCHAR2Converts a RAW value to a HEXADECIMAL value.RAWTOHEX('012')'303132'
INSTR(string, set, [ start [, occurrence ] ])INTEGERFinds 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 isn't found.INSTR('PETER PIPER PICKED a PECK of PICKLED PEPPERS','PI',1,3)30
INSTRB(string, set)INTEGERReturns the position of the set within the string. Returns 0 if set is not found.INSTRB('PETER PIPER PICKED a PECK of PICKLED PEPPERS', 'PICK')13
INSTRB(string, set, start)INTEGERReturns the position of the set within the string, beginning at start. Returns 0 if set is not found.INSTRB('PETER PIPER PICKED a PECK of PICKLED PEPPERS','PICK', 14)30
INSTRB(string, set, start, occurrence)INTEGERReturns the position of the specified occurrence of set within the string, beginning at start. Returns 0 if set is not found.INSTRB('PETER PIPER PICKED a PECK of PICKLED PEPPERS','PICK', 1, 2)30
LOWER(string)CLOBConverts string to lower case.LOWER('TOM')tom
NLS_CHARSET_ID(string)NUMBERReturns the character set ID number corresponding to the character set name string. The string argument is a runtime VARCHAR2 value.NLS_CHARSET_ID('utf8')6
NLS_CHARSET_NAME(number)VARCHAR2Returns the name of the character set corresponding to the ID number. The character set name is returned as a VARCHAR2 value in the database character set.NLS_CHARSET_NAME(6)UTF8
NLS_CHARSET_DECL_LEN(byte_count, char_set_id)NCHARReturns the declaration length in number of characters of an NCHAR column. The byte_count argument is the width of the column. The char_set_id argument is the character set ID of the column.NLS_CHARSET_DECL_LEN(100,nls_charset_id('utf8'))100
SUBSTR(string, start [, count ])CLOBExtracts substring starting from start and going for count characters. If count isn't specified, the string is clipped from the start till the end.SUBSTR('This is a test',6,2)is
SUBSTRB(string, start [, count ])CLOBSame as SUBSTR except start and count are in number of bytes.SUBSTRB('abc',3), assuming a double-byte character set.c
SUBSTR2(string, start [, count ])CLOBAlias for SUBSTR.SUBSTR2('This is a test',6,2)is
SUBSTR2(string, start [, count ])CLOBAlias for SUBSTRB.SUBSTR2('abc',3) (assuming a double-byte character set)c
SUBSTR4(string, start [, count ])CLOBAlias for SUBSTR.SUBSTR4('This is a test',6,2)is
SUBSTR4(string, start [, count ])CLOBAlias for SUBSTRB.SUBSTR4('abc',3) (assuming a double-byte character set)c
SUBSTRC(string, start [, count ])CLOBAlias for SUBSTR.SUBSTRC('This is a test',6,2)is
SUBSTRC(string, start [, count ])CLOBAlias for SUBSTRB.SUBSTRC('abc',3) (assuming a double-byte character set)c
TRIM(\[ LEADING | TRAILING | BOTH ] \[ characters ] FROM string)CLOBRemoves the longest string containing only the characters (a space by default) from the start, end, or both ends of the string.TRIM(BOTH 'x' FROM 'xTomxx')Tom
LTRIM(string [, set])CLOBRemoves all the characters specified in set from the left of a given string. If set isn't specified, a blank space is used by default.LTRIM('abcdefghi', 'abc')defghi
RTRIM(string [, set])CLOBRemoves all the characters specified in set from the right of a given string. If set isn't specified, a blank space is used by default.RTRIM('abcdefghi', 'ghi')abcdef
UPPER(string)CLOBConverts string to upper case.UPPER('tom')TOM

More 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 the above table.

FunctionReturn typeDescriptionExampleResult
ASCII(string)INTEGERASCII code of the first byte of the argument.ASCII('x')120
CHR(INTEGER)CLOBCharacter with the given ASCII code.CHR(65)A
DECODE(expr, expr1a, expr1b [, expr2a, expr2b ]... [, default ])Same as argument types of expr1b, expr2b,..., defaultFinds 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.DECODE(3, 1,'One', 2,'Two', 3,'Three', 'Not found')Three
INITCAP(string)CLOBConverts the first letter of each word to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.INITCAP('hi THOMAS')Hi Thomas
LENGTHINTEGERReturns the number of characters in a string value.LENGTH('Côte d''Azur')11
LENGTHCINTEGERThis function is identical in functionality to LENGTH; the function name is supported for compatibility.LENGTHC('Côte d''Azur')11
LENGTH2INTEGERThis function is identical in functionality to LENGTH; the function name is supported for compatibility.LENGTH2('Côte d''Azur')11
LENGTH4INTEGERThis function is identical in functionality to LENGTH; the function name is supported for compatibility.LENGTH4('Côte d''Azur')11
LENGTHBINTEGERReturns the number of bytes required to hold the given value.LENGTHB('Côte d''Azur')12
LPAD(string, length INTEGER [, fill ])CLOBFills 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).LPAD('hi', 5, 'xy')xyxhi
REPLACE(string, search_string [, replace_string ]CLOBReplaces one value in a string with another. If you don't specify a value for replace_string, the search_string value, when found, is removed.REPLACE( 'GEORGE', 'GE', 'EG')EGOREG
RPAD(string, length INTEGER [, fill ])CLOBFills 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.RPAD('hi', 5, 'xy')hixyx
TRANSLATE(string, from, to)CLOBAny character in string that matches a character in the from set is replaced by the corresponding character in the to set.TRANSLATE('12345', '14', 'ax')a23x5

Truncation of string text resulting from concatenation with NULL

Note

This functionality isn't compatible with Oracle databases, which can lead to some inconsistency when converting data from Oracle to EDB Postgres Advanced Server.

For EDB Postgres Advanced Server, when a column value is NULL, the concatenation of the column with a text string can result in either of the following:

  • Return of the text string
  • Disappearance of the text string (that is, a null result)

The result depends on the data type of the NULL column and the way in which the concatenation is done.

If you use the string concatenation operator ‘||’, then the types that have implicit coercion to text, as listed in the table Data Types with Implicit Coercion to Text, don't truncate the string if one of the input parameters is NULL. For other types, it truncates the string unless the explicit type cast is used (that is, ::text). Also, to see the consistent behavior in the presence of nulls, you can use the CONCAT function.

The following query lists the data types that have implicit coercion to text:

SELECT castsource::regtype, casttarget::regtype, castfunc::regproc,
    CASE castcontext
        WHEN 'e' THEN 'explicit'
        WHEN 'a' THEN 'implicit in assignment'
        WHEN 'i' THEN 'implicit in expressions'
    END as castcontext,
    CASE castmethod
        WHEN 'f' THEN 'function'
        WHEN 'i' THEN 'input/output function'
        WHEN 'b' THEN 'binary-coercible'
    END as castmethod
FROM pg_cast
    WHERE casttarget::regtype::text = 'text'
      AND castcontext='i';

The result of the query is listed in the following table.

castsourcecasttargetcastfunccastcontextcastmethod
charactertextpg_catalog.textimplicit in expressionsfunction
character varyingtext-implicit in expressionsbinary-coercible
"char"textpg_catalog.textimplicit in expressionsfunction
nametextpg_catalog.textimplicit in expressionsfunction
pg_node_treetext-implicit in expressionsbinary-coercible
pg_ndistincttext-implicit in expressionsinput/output function
pg_dependenciestext-implicit in expressionsinput/output function
integertext-implicit in expressionsinput/output function
smallinttext-implicit in expressionsinput/output function
oidtext-implicit in expressionsinput/output function
datetext-implicit in expressionsinput/output function
double precisiontext-implicit in expressionsinput/output function
realtext-implicit in expressionsinput/output function
time with time zonetext-implicit in expressionsinput/output function
time without time zonetext-implicit in expressionsinput/output function
timestamp with time zonetext-implicit in expressionsinput/output function
intervaltext-implicit in expressionsinput/output function
biginttext-implicit in expressionsinput/output function
numerictext-implicit in expressionsinput/output function
timestamp without time zonetext-implicit in expressionsinput/output function
recordtext-implicit in expressionsinput/output function
booleantextpg_catalog.textimplicit in expressionsfunction
byteatext-implicit in expressionsinput/output function

For information on the column output, see the pg_cast system catalog in the PostgreSQL core documentation.

So for example, data type UUID isn't in this list and therefore doesn't 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:

CREATE TABLE null_concat_types (
    boolean_type    BOOLEAN,
    uuid_type       UUID,
    char_type       CHARACTER
);

INSERT INTO null_concat_types VALUES (NULL, NULL, NULL);

Columns boolean_type and char_type have the implicit coercion to text while column uuid_type doesn't.

Thus, string concatenation with the concatenation operator ‘||‘ against columns boolean_type or char_type results in the following:

SELECT 'x=' || boolean_type || 'y' FROM null_concat_types;
Output
 ?column?
----------
 x=y
(1 row)
SELECT 'x=' || char_type || 'y' FROM null_concat_types;
Output
 ?column?
----------
 x=y
(1 row)

But concatenation with column uuid_type results in the loss of the x= string:

SELECT 'x=' || uuid_type || 'y' FROM null_concat_types;
Output
 ?column?
----------
 y
(1 row)

However, using explicit casting with ::text prevents the loss of the x= string:

SELECT 'x=' || uuid_type::text || 'y' FROM null_concat_types;
Output
 ?column?
----------
 x=y
(1 row)

Using the CONCAT function also preserves the x= string:

SELECT CONCAT('x=',uuid_type) || 'y' FROM null_concat_types;
Output
 ?column?
----------
 x=y
(1 row)

Thus, depending on the data type of a NULL column, use explicit casting or the CONCAT function to avoid losing some text strings.