The syntax has been extended from what the standard allows to allow the same target specification as the extended SUBSTRING function (see that function for details).

 

Syntax

 

OVERLAY (

 <character value expression> PLACING <character value expression>

 { FROM [ LEFT | RIGHT ] <integer expression>

   [ { FOR | TO } [ LEFT | RIGHT ] <integer expression> ]

   [ USING { CHARACTERS | OCTETS } ]

 | SIMILAR [ TO ] <character value expression> [ "IGNORE" "CASE" ]

   [ "GROUP" <character value or integer expression> ]

 }

)

 

Furthermore, OVERLAY has been extended to allow operating on DATE, TIME, and TIMESTAMP values.

 

In this case the syntax is:

OVERLAY (

 <date, time, or timestamp value expression> PLACING <integer or float value expression>

 IN {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }

 

 

Usage

 

The function allows to replace parts of a string with another string directly in place.

 

Here is an example of what is now possible:

 

DECLARE s NVARCHAR(70);

SET S = 'the blue king and the White king and the white queen';

 

SELECT

  OVERLAY(s PLACING 'the joker' SIMILAR TO 'the ((red|white) (king|queen))' IGNORE CASE),

  OVERLAY(s PLACING ' - gone - ' FROM LEFT 5 TO RIGHT 5),

  OVERLAY(s PLACING ' - gone - ' FROM 5)

FROM

  #dummy;

 

Resulting in the following 3 string:

the blue king and the joker and the white queen

the - gone - ueen

the - gone - and the White king and the white queen