vastorbit.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:

SQL string.

Return type:

StringSQL

Examples

For this example, we will use the Titanic dataset.

from vastorbit.datasets import load_titanic

titanic = load_titanic()

Note

vastorbit 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 vastorbit environment.

Now, let’s import the vastorbit SQL functions.

import vastorbit.sql.functions as vof

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

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

	Abc name Varchar(164) 100%	Abc new_title Varchar(176054) 100%
1	McCormack, Mr. Thomas Joseph	McCormack, [title here] Thomas Joseph
2	McCoy, Miss. Agnes	McCoy, [title here] Agnes
3	McCoy, Miss. Alicia	McCoy, [title here] Alicia
4	McCoy, Mr. Bernard	McCoy, [title here] Bernard
5	McDermott, Miss. Brigdet Delia	McDermott, [title here] Brigdet Delia

Note

It’s crucial to utilize vastorbit 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. vastorbit effectively manages these changes, a task not achievable with pure SQL.