replace_sections

NAME

replace_sections(): search and replace text appearing between section start/end

TYPE

Function

DESCRIPTION

Replace sections in a text, identified by start/end tokens, with a given replacement string. Back-referencing supported.

This function allows to replace one or more occurrences of text with a replacement text. The replaced text is such that appears between a section start and section end texts. The section start/end texts themselves are stripped during the replacement process.

The replacement text may be constant, but may also use the '\\0' back-reference to indicate the original text.

SYNOPSIS

replace_sections(
    txt TEXT CHARSET utf8, 
    section_start TEXT charset utf8,
    section_end TEXT charset utf8,
    replacement_text TEXT CHARSET utf8
  ) 
  RETURNS TEXT CHARSET utf8
  NO SQL

Input:

  • txt: text to manipulate
  • section_start: text identifying beginning of section
  • section_end: text identifying end of section
  • replacement_text: text to inject in place of sections. May contain the special '\\0' back-reference
    • .

EXAMPLES

Strip anything between braces:

mysql> select replace_sections('Me (myself) I', '(', ')', '') as result;
+--------+
| result |
+--------+
| Me  I  |
+--------+

Remove all HTML tags, assume "<" & ">" do not appear throughout text:

mysql> select replace_sections('The <b>quick</b> brown <b>fox</b>', 
        '<', '>', '') as result;
+---------------------+
| result              |
+---------------------+
| The quick brown fox |
+---------------------+

Remove only <b> HTML tags, retain enclosed text:

mysql> select replace_sections(
        'The <b>quick</b> <span>brown</span> <b>fox</b>', 
        '<b>', '</b>', 
        '\\0') as result;
+----------------------------------+
| result                           |
+----------------------------------+
| The quick <span>brown</span> fox |
+----------------------------------+

Convert b tags to span tags, retain enclosed text:

mysql> select replace_sections(
        'The <b>quick</b> brown <b>fox</b>', 
        '<b>', '</b>', 
        '<span>\\0</span>') as result;
+-----------------------------------------------+
| result                                        |
+-----------------------------------------------+
| The <span>quick</span> brown <span>fox</span> |
+-----------------------------------------------+

ENVIRONMENT

MySQL 5.1 or newer

SEE ALSO

replace_all(), trim_wspace()

AUTHOR

Shlomi Noach
 
common_schema documentation