CHANGE

Read syntax diagramSkip visual syntax diagram
Syntax

                                                   .-1-----.   
>>-CHANGE--(--haystack--,--+-old--,--+-----+-+--,--+-------+---->
                           |         '-new-' |     '-count-'   
                           '-,--new----------'                 

      .-1-----.     .-0------.      
>--,--+-------+--,--+--------+--)------------------------------><
      '-start-'     '-length-'      

Note: Commas following the last specified argument can be omitted.

Searches haystack and changes one or more occurrences of old to new.

Returns
Returns haystack, with up to count occurrences of the substring old changed to the substring new.
haystack
The string that you want to search.
old
Old string to change. If this argument is omitted, the new string is inserted at the start location.
new
New string. If this argument is omitted, then count occurrences of old are deleted.
count
Maximum number of occurrences of old to change. Must be a non-negative integer. Default value is 1. A value of 0 indicates that all occurrences should be changed, unless the old string field is omitted, in which case it is equivalent to a value of 1.
start
Position in haystack in bytes at which to start searching for occurrences of old. Must be a positive integer. The default value is 1. If start is greater than the current length of the output record, the function has no effect.
length
Number of bytes within haystack to search for occurrences of old. Must be a non-negative integer. A value of 0 indicates that the remainder of haystack from start should be searched. If length is less than the length of old, the function has no effect.
Example 1
CHANGE('abcabcabc','abc','DeF')      ►    'DeFabcabc'
        /*  1 (default) occurrence of old changed           */
Example 2
CH('abcabcabc','abc','DeF',2)        ►    'DeFDeFabc'
        /*  2 occurrences of old changed                    */
Example 3
CHANGE('abcabcabc','abc','DeF',0)    ►    'DeFDeFDeF'
       /*  count = 0, all occurrences of old changed        */
Example 4
CH('abcabcabc','abc','DeF',,4)       ►    'abcDeFabc'
       /*  1 (default) occurrences of old changed,          */
       /*  starting at position 4                           */
Example 5
CHANGE('aaaaaaaa','a','A',0,3,2)     ►    'aaAAaaaa'
       /*  all occurrences of old changed, starting at      */
       /*  position 3 for a length of 2                     */
Example 6
CH('abcabcabc','a',,0)               ►    'bcbcbc'
       /*  new omitted, count = 0,                          */
       /*  all occurrences of old deleted                   */
Example 7
CHANGE('abc',,'def',,2)              ►    'adefbc'
       /*  old omitted, new inserted, starting at           */
       /*  position 2                                       */