SqlExpression OVERLAY
From Eigenpedia
Contents |
Syntax
OVERLAY( source_string PLACING new_string FROM start_position [ FOR length ] )
Purpose
Replaces the substring within source_string starting at start_position for length number of characters, with a new substring new_string. The length of the substring being replaced does not need to be the same as the length of the replacing substring.
Input
- source_string [VARCHAR/CHAR] - The string which contains substring to be replaced
- new_string [VARCHAR/CHAR] - The new substring used to replace the section within the source_string
- start_position [INTEGER] - The position to begin replacing within the source_string.
- If this is larger than the length of the source_string, the new_string will be added on to the end of the source_string.
- If it is negative or 0, the new_string will be added to the beginning of the source_string.
- string_length [INTEGER] - Optional parameter which specifies the length of the substring to be replaced. This defaults to the length of the replacing substring.
Output
- [VARCHAR] - the resulting string after new_string has replaced the substring within source_string defined by start_position and length. Returns null if any of the inputs are null.
Example
| Function | Result |
|---|---|
| OVERLAY( 'abcderegewfwerwe' placing '1234567890' from 15 for 1 ) | abcderegewfwer1234567890e |
| OVERLAY( 'This is a new sentence. 2nd one is here.' placing '123' from 10) | This is a123w sentence. 2nd one is here. |
| OVERLAY( CAST('ABCdef' AS VARCHAR(10)) PLACING CAST('abc' as CHAR(5)) FROM 1 FOR 2) | abc Cdef |
