verticapy.sql.functions.regexp_replace#

Replace all occurrences of a substring that match a regular expression with another substring.

Parameters#

expr: SQLExpression: Expression.
target: SQLExpression: The regular expression to search for within the string.
replacement: SQLExpression: The string to replace matched substrings.
position: int, optional: The number of characters from the start of the string where the function should start searching for matches.
occurrence: int, optional: Controls which occurrence of a pattern match in the string to return.

Returns#

StringSQL: SQL string.

Examples#

For this example, we will use the Titanic dataset.

from verticapy.datasets import load_titanic

titanic = load_titanic()

Note

VerticaPy offers a wide range of sample datasets that are ideal for training and testing purposes. You can explore the full list of available datasets in the Datasets, which provides detailed information on each dataset and how to use them effectively. These datasets are invaluable resources for honing your data analysis and machine learning skills within the VerticaPy environment.

Now, let’s import the VerticaPy SQL functions.

import verticapy.sql.functions as vpf

Now, let’s go ahead and apply the function.

titanic["new_title"] = vpf.regexp_replace(
    titanic["name"],
    '([A-Za-z])+\.',
    '[title here] ',
)
display(titanic[["name", "new_title"]])

	Abc Varchar(164) 100%	Abc Varchar(2856) 100%
1
2
3
4
5

Note

It’s crucial to utilize VerticaPy SQL functions in coding, as they can be updated over time with new syntax. While SQL functions typically remain stable, they may vary across platforms or versions. VerticaPy effectively manages these changes, a task not achievable with pure SQL.