Skip to main content

DB_Column.text_replace

text_replaceterm new_textcase_sensitivityonly_first

Group: Text
Aliases: regex, substitute

Documentation​

Replaces the first, or all occurrences of term with new_text in each row. If term is empty, the function returns the table unchanged. This method follows the exact replacement semantics of the Text.replace method. If regex is used the replacement string can contain references to groups matched. The following syntaxes are supported: $0: the entire match string $&: the entire match string $n: the nth group $<foo>: Named group foo The exact syntax of the regular expression is dependent on the database engine.

Arguments​

  • term: The term to find.
  • replacement: The text to replace matches with.
  • case_sensitivity: Specifies if the text values should be compared case sensitively.
  • only_first: If True, only replace the first match.

Examples​

Replace dashes with underscores.​

      column.text_replace "-" "_"

Remove leading and trailing spaces from cells.​

      column.text_replace "^\s*(.*?)\s*$".to_regex "$1"

Replace texts in quotes with parentheses.​

      column.text_replace '"(.*?)"'.to_regex '($1)'

Remarks​

Backend Support​

Each database backend supports different combinations of options:

Text: +----------------+------------+----------+--------+ | case_sensitive | only_first | postgres | sqlite | +----------------+------------+----------+--------+ | t | f | ✓ | ✓ | | t | t | ✓ | ✓ | | f | f | ✓ | ✗ | | f | t | ✓ | ✓ | +----------------+------------+----------+--------+

Regex: +----------------+------------+----------+--------+ | case_sensitive | only_first | postgres | sqlite | +----------------+------------+----------+--------+ | t | f | ✓ | ✗ | | t | t | ✓ | ✗ | | f | f | ✓ | ✗ | | f | t | ✓ | ✗ | +----------------+------------+----------+--------+

Text Column: +----------------+------------+----------+--------+ | case_sensitive | only_first | postgres | sqlite | +----------------+------------+----------+--------+ | t | f | ✓ | ✓ | | t | t | ✗ | ✓ | | f | f | ✗ | ✗ | | f | t | ✗ | ✓ | +----------------+------------+----------+--------+