On this page

Show all

Help Center/ Data Lake Insight/ Spark SQL Syntax Reference/ Built-In Functions/ String Functions/ regexp_replace

regexp_replace

Updated on 2023-10-25 GMT+08:00

View PDF

This function has slight variations in its functionality depending on the version of Spark being used.

For Spark 2.4.5 or earlier: Replaces the substring that matches pattern in the string source with the specified string replace_string and returns the result string.
For Spark 3.3.1: Replaces the substring that matches the pattern for the occurrence time in the source string and the substring that matches the pattern later with the specified string replace_string and returns the result string.

Similar function: regexp_replace1. The regexp_replace1 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. However, the egexp_replace1 function applies only to Spark 2.4.5 or earlier.

The regexp_replace1 function is applicable to Spark 2.4.5. It supports specifying the occurrence parameter, whereas the regexp_replace function does not support it.

Syntax

Spark 2.4.5 or earlier

regexp_replace(string <source>, string <pattern>, string <replace_string>)

Spark 3.1.1

regexp_replace(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. NOTE: This parameter is available only when Spark 3.1.1 is used.

Return Values

The return value is of the STRING type.

NOTE:

If the value of pattern is an empty string or there is no group in pattern, an error is reported.
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

For Spark 2.4.5 or earlier
The value num-num is returned.
```
SELECT regexp_replace('100-200', '(\\d+)', 'num'); 
```

For Spark 3.1.1

The value 2222 is returned.

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

The value 2222 is returned.

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

The value a222 is returned.

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

The value ab22 is returned.

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

The value abc2 is returned.

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

Parent topic: String Functions

Previous topic: replace

Next topic: regexp_replace1

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