regexp_replace1

Updated on 2025-01-22 GMT+08:00

View PDF

This function is used to replace the substring that matches pattern for the occurrence time in the source string with the specified string replace_string and return the result string.

NOTE:

This function applies only to Spark 2.4.5 or earlier.

Similar function: regexp_replace. The regexp_replace function has slightly different functionalities for different versions of Spark. For details, see regexp_replace.

Syntax

regexp_replace1(string <source>, string <pattern>, string <replace_string>[, bigint <occurrence>])

Parameters

**Table 1** Parameters
Parameter	Mandatory	Type	Description
source	Yes	STRING	String to be replaced
pattern	Yes	STRING	Constant or regular expression of the STRING type. Pattern to be matched. For more guidelines on writing regular expressions, refer to the regular expression specifications. If the value of this parameter is an empty string, an error is reported.
replace_string	Yes	STRING	String that replaces the one matching the pattern parameter
occurrence	No	BIGINT	The value must be greater than or equal to 1, indicating that the string that is matched for the occurrence time is replaced with replace_string. When the value is 1, all matched substrings are replaced. If the value is of another type or is less than 1, an error is reported. The default value is 1.

Return Values

The return value is of the STRING type.

NOTE:

If a group that does not exist is referenced, the group is not replaced.
If the value of replace_string is NULL and the pattern is matched, NULL is returned.
If the value of replace_string is NULL but the pattern is not matched, NULL is returned.
If the value of source, pattern, or occurrence is NULL, NULL is returned.

Example Code

The value 2222 is returned.

select regexp_replace1('abcd', '[a-z]', '2');

The value 2bcd is returned.

select regexp_replace1('abcd', '[a-z]', '2', 1);

The value a2cd is returned.

select regexp_replace1('abcd', '[a-z]', '2', 2);

The value ab2d is returned.

select regexp_replace1('abcd', '[a-z]', '2', 3);

The value abc2 is returned.

select regexp_replace1('abcd', '[a-z]', '2', 4);

Parent topic: String Functions

Previous topic: regexp_replace

Next topic: regexp_instr

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot