String Functions and Operators v11

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.

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

Truncation of String Text Resulting from Concatenation with NULL

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:

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

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 Data Types with Implicit Coercion to Text 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.

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:

For information on the column output, see the pg_cast system catalog in the PostgreSQL core documentation available at:

https://www.postgresql.org/docs/11/static/catalog-pg-cast.html

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.

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 does not.

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;

 ?column?
----------
 x=y
(1 row)

SELECT 'x=' || char_type || 'y' FROM null_concat_types;

 ?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;

 ?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;

 ?column?
----------
 x=y
(1 row)

Using the CONCAT function also preserves the x= string:

SELECT CONCAT('x=',uuid_type) || 'y' FROM null_concat_types;

 ?column?
----------
 x=y
(1 row)

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.

• On this page
• Truncation of String Text Resulting from Concatenation with NULL