OVERLAY scalar function

The OVERLAY function returns a string in which, beginning at start in source-string, length of the specified code units have been deleted and insert-string has been inserted.

Read syntax diagramSkip visual syntax diagramOVERLAY(source-string PLACINGinsert-stringFROMstartFORlengthUSINGCODEUNITS16CODEUNITS32OCTETS,insert-string,start,length,CODEUNITS16CODEUNITS32OCTETS )

The schema is SYSIBM.

source-string
An expression that specifies the source string. The expression must return a value that is a built-in string, numeric, or datetime data type. If the value is not a string data type, it is implicitly cast to VARCHAR before evaluating the function.
insert-string
An expression that specifies the string to be inserted into source-string, starting at the position identified by start. The expression must return a value that is a built-in string, numeric, or datetime data type. If the value is not a string data type, it is implicitly cast to VARCHAR before evaluating the function. If the code page of the insert-string differs from that of the source-string, insert-string is converted to the code page of the source-string.
start
An expression that returns an integer value. The integer value specifies the starting point within the source string where the deletion and the insertion of another string is to begin. The expression must return a value that is a built-in numeric, CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type. If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function. The integer value is the starting point in code units using the specified string units. The integer value must be between 1 and the actual length of source-string in the specified string units plus one (SQLSTATE 42815). If OCTETS is specified and the result is graphic data, the value must be an odd number between 1 and the actual octet length of source-string plus one (SQLSTATE 428GC or 22011).
length
An expression that specifies the number of code units (in the specified string units) that are to be deleted from the source string, starting at the position identified by start. The expression must return a value that is a built-in numeric, CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type. If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function. The value must be a positive integer or zero (SQLSTATE 22011). If OCTETS is specified and the result is graphic data, the value must be an even number or zero (SQLSTATE 428GC).

Not specifying length is equivalent to specifying a value of 1, except when OCTETS is specified and the result is graphic data, in which case, not specifying length is equivalent to specifying a value of 2.

CODEUNITS16, CODEUNITS32, or OCTETS
Specifies the string unit of start and length.

CODEUNITS16 specifies that start and length are expressed in 16-bit UTF-16 code units. CODEUNITS32 specifies that start and length are expressed in 32-bit UTF-32 code units. OCTETS specifies that start and length are expressed in bytes.

If the string unit is specified as CODEUNITS16 or CODEUNITS32, and the result is a binary string or a FOR BIT DATA string, an error is returned (SQLSTATE 428GC). If the string unit is specified as CODEUNITS16 or OCTETS, and the string units of source-string is CODEUNITS32, an error is returned (SQLSTATE 428GC). If the string unit is specified as OCTETS, the operation is performed in the code page of the source-string.

If a string unit argument is not specified and both source-string and insert-string are either a character string that is not FOR BIT DATA or is a graphic string, the default is CODEUNITS32. Otherwise, the default is OCTETS.

For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see String units in built-in functions in Character strings.

The data type of the result depends on the data types of source-string and insert-string, as shown in the following tables of supported type combinations. The string unit of the result is the string unit of source-string. If either source-string or insert-string is defined as FOR BIT DATA the other argument cannot be defined with string units of CODEUNITS32. The second table applies to Unicode databases only.

Table 1. Data type of the result as a function of the data types of source-string and insert-string
source-string insert-string Result
CHAR or VARCHAR CHAR or VARCHAR VARCHAR
GRAPHIC or VARGRAPHIC GRAPHIC or VARGRAPHIC VARGRAPHIC
CLOB CHAR, VARCHAR, or CLOB CLOB
CHAR or VARCHAR CLOB CLOB
DBCLOB GRAPHIC, VARGRAPHIC, or DBCLOB DBCLOB
GRAPHIC or VARGRAPHIC DBCLOB DBCLOB
CHAR or VARCHAR CHAR FOR BIT DATA or VARCHAR FOR BIT DATA VARCHAR FOR BIT DATA
CHAR FOR BIT DATA or VARCHAR FOR BIT DATA CHAR, VARCHAR, CHAR FOR BIT DATA, or VARCHAR FOR BIT DATA VARCHAR FOR BIT DATA
BINARY or VARBINARY BINARY or VARBINARY VARBINARY
BLOB BLOB BLOB
BINARY or VARBINARY BLOB BLOB
Note: If the data types for source-string and insert-string are a combination of binary and FOR BIT DATA string, the argument that is not a binary data type is handled as if it was cast to the corresponding binary data type.
Table 2. Data type of the result as a function of the data types of source-string and insert-string (Unicode databases only)
source-string insert-string Result
CHAR or VARCHAR GRAPHIC or VARGRAPHIC VARCHAR
GRAPHIC or VARGRAPHIC CHAR or VARCHAR VARGRAPHIC
CLOB GRAPHIC, VARGRAPHIC, or DBCLOB CLOB
DBCLOB CHAR, VARCHAR, or CLOB DBCLOB

A source-string can have a length of 0; in this case, start must be 1 (as implied by the bounds provided in the description for start), and the result of the function is a copy of the insert-string.

An insert-string can also have a length of 0. This has the effect of deleting the code units identified by start and length from the source-string.

The length attribute of the result is the length attribute of source-string plus the length attribute of insert-string when the string units of the source-string and insert-string are the same or the result string units is CODEUNITS32. Special cases are listed in the following table.
Table 3. Data type of the result as a function of the data types of source-string and insert-string (special cases, Unicode databases only)
source-string insert-string Result
Data type String units Data type String units Length attribute String units
Character string with length attribute A OCTETS Graphic string with length attribute B CODEUNITS16 A+3*B OCTETS
      CODEUNITS32 A+4*B  
    Character with length attribute B CODEUNITS32 A+4*B  
Graphic string with length attribute A CODEUNITS16 Character with length attribute B OCTETS A+B CODEUNITS16
      CODEUNITS32 A+2*B  
    Graphic string with length attribute B CODEUNITS32 A+2*B  
The actual length of the result depends on the actual length of source-string, the actual length of the deleted string, the actual length of the insert-string, and string units used for the start and length arguments. For example, if the string arguments are character strings in OCTETS and the OCTETS is used as the fourth argument, the actual length of the result is A1 - MIN((A1 - V2 + 1), V3) + A4, where:
  • A1 is the actual length of source-string
  • V2 is the value of start
  • V3 is the value of length
  • A4 is the actual length of insert-string
If the actual length of the result string exceeds the maximum for the return data type, an error is returned (SQLSTATE 54006).

If any argument can be null, the result can be null; if any argument is null, the result is the null value.

Examples

  • Example 1: Create the strings 'INSISTING', 'INSISERTING', and 'INSTING' from the string 'INSERTING' by inserting text into the middle of the existing text.
       SELECT OVERLAY('INSERTING','IS',4,2,OCTETS),
     OVERLAY('INSERTING','IS',4,0,OCTETS),
     OVERLAY('INSERTING','',4,2,OCTETS)
     FROM SYSIBM.SYSDUMMY1
  • Example 2: Create the strings 'XXINSERTING', 'XXNSERTING', 'XXSERTING', and 'XXERTING' from the string 'INSERTING' by inserting text before the existing text, using 1 as the starting point.
       SELECT OVERLAY('INSERTING','XX',1,0,CODEUNITS16)),
     OVERLAY('INSERTING','XX',1,1,CODEUNITS16)),
     OVERLAY('INSERTING','XX',1,2,CODEUNITS16)),
     OVERLAY('INSERTING','XX',1,3,CODEUNITS16))
     FROM SYSIBM.SYSDUMMY1
  • Example 3: Create the string 'ABCABCXX' from the string 'ABCABC' by inserting text after the existing text. Because the source string is 6 characters long, set the starting position to 7 (one plus the length of the source string).
       SELECT OVERLAY('ABCABC','XX',7,0,CODEUNITS16))
     FROM SYSIBM.SYSDUMMY1
  • Example 4: Change the string 'Hegelstraße' to 'Hegelstrasse'.
       SELECT OVERLAY('Hegelstraße','ss',10,1,CODEUNITS16))
     FROM SYSIBM.SYSDUMMY1
  • Example 5: The following example works with the Unicode string '&N~AB', where '&' is the musical symbol G clef character, and '~' is the combining tilde character. This string is shown in different Unicode encoding forms in the following example:
      '&' 'N' '~' 'A' 'B'
    UTF-8 X'F09D849E' X'4E' X'CC83' X'41' X'42'
    UTF-16BE X'D834DD1E' X'004E' X'0303' X'0041' X'0042'
    Assume that the variables UTF8_VAR and UTF16_VAR contain the UTF-8 and the UTF-16BE representations of the string, respectively. Use the OVERLAY function to insert a 'C' into the Unicode string '&N~AB'.
       SELECT OVERLAY(UTF8_VAR, 'C', 1, CODEUNITS16),
     OVERLAY(UTF8_VAR, 'C', 1, CODEUNITS32),
     OVERLAY(UTF8_VAR, 'C', 1, OCTETS)
   FROM SYSIBM.SYSDUMMY1
    returns the values 'C?N~AB', 'CN~AB', and 'CbbbN~AB', respectively, where '?' represents X'EDB49E', which corresponds to the X'DD1E' in the intermediate UTF-16 form, and 'bbb' replaces the UTF-8 incomplete characters X'9D849E'.
       SELECT OVERLAY(UTF8_VAR, 'C', 5, CODEUNITS16),
     OVERLAY(UTF8_VAR, 'C', 5, CODEUNITS32),
     OVERLAY(UTF8_VAR, 'C', 5, OCTETS)
   FROM SYSIBM.SYSDUMMY1
    returns the values '&N~CB', '&N~AC', and '&N~AB', respectively.
       SELECT OVERLAY(UTF16_VAR, 'C', 1, CODEUNITS16),
     OVERLAY(UTF16_VAR, 'C', 1, CODEUNITS32)
   FROM SYSIBM.SYSDUMMY1
    returns the values 'C?N~AB' and 'CN~AB', respectively, where '?' represents the unmatched low surrogate U+DD1E.
       SELECT OVERLAY(UTF16_VAR, 'C', 5, CODEUNITS16),
     OVERLAY(UTF16_VAR, 'C', 5, CODEUNITS32)
   FROM SYSIBM.SYSDUMMY1
    returns the values '&N~CB' and '&N~AC', respectively.