Replace All Occurrences using Oracle SQL regexp_replace Case-insensitive

Question

I'd love to replace a case-insensitive string for all occurrences

The given query replaces all tons to yard. However, it is case sensitive.

SELECT regexp_replace(col_name, 'tons', 'yard') FROM DUAL;

How can I write a query that replaces all occurrences regardless of letter cases. I tried this but didn't work:

SELECT regexp_replace(col_name, 'tons', 'yard', 'i') FROM DUAL;

Thanks

Solution

Using the 'i' option is correct, but you're missing two parameters before it.

REGEXP_REPLACE(<source_string>, <pattern>,<replace_string>, <position>, <occurrence>, <match_parameter>)

For position, use 1 to start searching at the beginning. For occurrence, use 0 to replace every occurrence.

SELECT regexp_replace(col_name, 'tons', 'yard', 1, 0, 'i') FROM DUAL;

Examples

Official documentation