"Replace" Function Examples

replace function examples

If you look at the function (by typing it's name at the console) you will see that it is just a simple functionalized version of the [<- function which is described at ?"[". [ is a rather basic function to R so you would be well-advised to look at that page for further details. Especially important is learning that the index argument (the second argument in replace can be logical, numeric or character classed values. Recycling will occur when there are differing lengths of the second and third arguments:

You should "read" the function call as" "within the first argument, use the second argument as an index for placing the values of the third argument into the first":

> replace( 1:20, 10:15, 1:2)
 [1]  1  2  3  4  5  6  7  8  9  1  2  1  2  1  2 16 17 18 19 20

Character indexing for a named vector:

> replace(c(a=1, b=2, c=3, d=4), "b", 10)
 a  b  c  d 
 1 10  3  4 

Logical indexing:

> replace(x <- c(a=1, b=2, c=3, d=4), x>2, 10)
 a  b  c  d 
 1  2 10 10 

Replace function in SQL

You can't use 3 parameters in a REPLACE function. Instead you can use it twice just like below :

SELECT REPLACE(REPLACE('SQL-Tu torial', '-', ''), ' ', '');

Output :

SQLTutorial

EXCEL - Replace function | Replace value from one column to replace with other column value

EDIT : For English users replace ; by , in the formula

You can use SEARCH to get the position of the $ and REPLACE everything after it.

For example with schema name in column A and schema value in column B -> result in column C.

To find the $ you need to use SEARCH("$";A2) it will give you the position.
Then you can count the number of characters to replace after the "$" by substracting the position to the length of the schema name with LEN(). (+1 to get the last char)

Then you can combine everything :

=REPLACE(A2;SEARCH("$";A2);LEN(A2)-SEARCH("$";A2)+1;B2)

Result in my Excel :

Replace function in SQL Server

If you do the same replacement twice, any number of sequential commas will get handled.

REPLACE(REPLACE(FileData, ',,' , ',NULL,'), ',,' , ',NULL,')

The first REPLACE deals with all the odd positions...

',,,,,,,,'` => ',NULL,,NULL,,NULL,,NULL,'

Doing it again will deal with all of the remaining positions.

=> ',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'

Note, by specifically handling a special case of three consecutive commas (as in an other answer here) you won't handle four or five or six, etc. The above solution generalises to Any length of consecutive commas.

To be fully robust, you may also need to consider when there is a missing NULL at the first or last place in the string.

[,ThatOneToMyLeft,and,ThatOneToMyRight,]

A laborious but robust approach could be to replace [, and ,] with [,, and ,,] respectively, then do the double-replacement, then undo the first steps...

REPLACE(
  REPLACE(
    REPLACE(
      REPLACE(
        REPLACE(
          REPLACE(
            FileData,
            '[,',
            '[,,'
          ),
          ',]',
          ',,]'
        ),
        ',,',
        ',NULL,'
      ),
      ',,',
      ',NULL,'
    ),
    ',]',
    ']',
  ),
  '[,',
  '['
)

There are ways to make even that less verbose, but I have to run right now :)

Excel find and replace function correct formula

Quite a long formula but in Excel O365 you could use:

=SUBSTITUTE(CONCAT(FILTERXML("<t><s>"&SUBSTITUTE(CONCAT(IF(MID(A1,SEQUENCE(LEN(A1)),1)="「","</s><s>「",IF(MID(A1,SEQUENCE(LEN(A1)),1)="」","」</s><s>",MID(A1,SEQUENCE(LEN(A1)),1)))),"<br>","|$|")&"</s></t>","//s[not(contains(., '「') and contains(., '―') and contains(., '/') and contains(., '」'))][node()]")),"|$|","<br>")

As long as you have access to CONCAT you could also do this in Excel 2019 but you'll have to swap SEQUENCE(LEN(A1)) for ROW(A$1:INDEX(A:A,LEN(A1)))

How to replace multiple substrings of a string?

Here is a short example that should do the trick with regular expressions:

import re

rep = {"condition1": "", "condition2": "text"} # define desired replacements here

# use these three lines to do the replacement
rep = dict((re.escape(k), v) for k, v in rep.iteritems()) 
#Python 3 renamed dict.iteritems to dict.items so use rep.items() for latest versions
pattern = re.compile("|".join(rep.keys()))
text = pattern.sub(lambda m: rep[re.escape(m.group(0))], text)

For example:

>>> pattern.sub(lambda m: rep[re.escape(m.group(0))], "(condition1) and --condition2--")
'() and --text--'

---

Related Topics