vastorbit.sql.functions.regexp_instr¶

Returns the starting or ending position in a string where a regular expression matches.

Parameters:

expr (SQLExpression) – Expression.
pattern (SQLExpression) – The regular expression to search for within the string.
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.
return_position (int, optional) – Sets the position within 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["title_start"] = vof.regexp_instr(
    titanic["name"],
    r'([A-Za-z])+\.',
    return_position = 0,
)
display(titanic[["name", "title_start"]])

	Abc name Varchar(164) 100%	123 title_start Integer 100%
1	McCormack, Mr. Thomas Joseph	12
2	McCoy, Miss. Agnes	8
3	McCoy, Miss. Alicia	8
4	McCoy, Mr. Bernard	8
5	McDermott, Miss. Brigdet Delia	12

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.