| | |
- regexp_replace(source_string, regexp_string, replace_string, position_arg, occurrence_arg, match_arg)
- DESCRIPTION:
Function replaces portions of source_string that match regexp_string with
the replace_string.
PARAMETERS:
source_string:
Required Argument.
Specifies a ColumnExpression of a string column or a string literal
to replace portion that matches regexp_string with the replace_string.
Format for the argument: '<dataframe>.<dataframe_column>.expression'.
If source_string is NULL, NULL is returned.
regexp_string:
Required Argument.
Specifies a ColumnExpression of a string column or a string literal
to use for regex matching.
Format for the argument: '<dataframe>.<dataframe_column>.expression'.
If regexp_string is NULL, NULL is returned.
replace_string:
Required Argument.
Specifies a ColumnExpression of a string column or a string literal
to use as replacement.
Format for the argument: '<dataframe>.<dataframe_column>.expression'.
If a replace_string is not specified, is NULL or is an empty string, the matches are
removed from the result.
position_arg:
Optional Argument.
A numeric argument.
Specifies the position in source_string from which to start searching.
Argument accepts a ColumnExpression of a numeric column or a numeric constant.
If the value greater than the input string length, NULL is returned.
If the value is NULL, the value NULL is returned.
If argument is not specified, the default value (1) is used.
occurrence_arg:
Optional Argument.
Specifies the occurrence to replace the match with replace_string.
Argument accepts a ColumnExpression of a numeric column or a numeric constant.
* If a value of 0 is specified, all occurrences are replaced.
* If the value is greater than 1, the search begins for the second
occurrence beginning with the first character following the first
occurrence of the regexp_string, and so on.
If occurrence_arg is greater than the number of matches found, nothing
is replaced and source_string is returned.
If occurrence_arg is NULL, a NULL result is returned. If occurrence_arg
is not specified, 0 is the default value.
match_arg:
Optional Argument.
Specifies a character which decides the handling of regex matching.
Valid values are:
* 'i' = case-insensitive matching.
* 'c' = case sensitive matching.
* 'n' = the period character (match any character) can match the newline character.
* 'm' = source_string is treated as multiple lines instead of as a single line.
With this option, the '^' and '$' characters apply to each line in source_string
instead of the entire source_string.
* 'l' = if source_string exceeds the current maximum allowed source_string size
(currently 16 MB), a NULL is returned instead of an error. This is useful for
long-running queries where you do not want long strings causing an error that
would make the query fail.
* 'x' = ignore whitespace.
The argument can contain more than one character.
Notes:
1. If a character in the argument is not valid, then that character is ignored.
2. If match_arg is not specified, is NULL, or is empty:
a. The match is case-sensitive.
b. A period does not match the newline character.
c. source_string is treated as a single line.
NOTE:
Function accepts positional arguments only.
EXAMPLES:
# Load the data to run the example.
>>> load_example_data("dataframe", "admissions_train")
>>>
# Create a DataFrame on 'admissions_train' table.
>>> admissions_train = DataFrame("admissions_train")
>>> admissions_train
masters gpa stats programming admitted
id
22 yes 3.46 Novice Beginner 0
36 no 3.00 Advanced Novice 0
15 yes 4.00 Advanced Advanced 1
38 yes 2.65 Advanced Beginner 1
5 no 3.44 Novice Novice 0
17 no 3.83 Advanced Advanced 1
34 yes 3.85 Advanced Beginner 0
13 no 4.00 Advanced Novice 1
26 yes 3.57 Advanced Advanced 1
19 yes 1.98 Advanced Advanced 0
>>>
# Example searches for "vice" substring from "stats" column and replaces
# the same with "w You See Me".
# Import func from sqlalchemy to execute regexp_replace() function.
>>> from sqlalchemy import func
# Create a sqlalchemy Function object.
# Note: Function name is case-insensitive.
>>> regexp_replace_ = func.Regexp_Replace(admissions_train.stats.expression, "vice",
... "w You See Me", 1, 1, 'c')
>>>
# Pass the Function object as input to DataFrame.assign().
>>> df = admissions_train.assign(regexp_replace_col=regexp_replace_)
>>> print(df)
masters gpa stats programming admitted regexp_replace_col
id
15 yes 4.00 Advanced Advanced 1 Advanced
7 yes 2.33 Novice Novice 1 Now You See Me
22 yes 3.46 Novice Beginner 0 Now You See Me
17 no 3.83 Advanced Advanced 1 Advanced
13 no 4.00 Advanced Novice 1 Advanced
38 yes 2.65 Advanced Beginner 1 Advanced
26 yes 3.57 Advanced Advanced 1 Advanced
5 no 3.44 Novice Novice 0 Now You See Me
34 yes 3.85 Advanced Beginner 0 Advanced
40 yes 3.95 Novice Beginner 0 Now You See Me
>>>
|