Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

OVERLAY SQL Function

Syntax

OVERLAY ( char_exp1 PLACING char_exp2 FROM start_position

[ FOR length ] )

The argument length is optional. If length is not specified, the scalar function returns a character string where char_exp2 has been appended to the char_exp1 beginning at start_position.

Description

The scalar function OVERLAY returns a character string where length characters have been deleted from the char_exp1 beginning at start_position and char_exp2 has been inserted into char_exp1 beginning at start_position.

Example

SELECT OVERLAY( name PLACING 'Technologies' FROM 9 FOR 12 )

From customers

WHERE name = 'FairCom Corporation' ;

OVERLAY(NAME,8,9,TECHNOLOGIES)

--------------------

FairCom Technologies

1 record selected

Details

  • The char_exp can be of fixed length or variable character types.
  • The start_position and length can be of the type INTEGER, SMALLINT, TINYINT or BIGINT.
  • The char_exp2 has to belong to char_exp1’s character set.
  • The result expression is of the type char_exp1 and will belong to the same character set as char_exp1.
  • If start_position is negative or zero, the result expression evaluates to a null.
  • If length is negative the result evaluates to a null.

Note: SQL-99 compatible

TOCIndex