Replacing multiple occurrence by a single occurrence in SAP HANA

The code you are using \1+ just removes consecutive occurrences only. To replace multiple occurrences of any character with a single occurrence, you can use the following approach with REPLACE_REGEXPR function in SAP HANA.

Using Regular Expression to Remove Multiple Occurrences

The regular expression pattern (.)(?=.*\1) works by capturing any character and looking ahead to see if the same character appears later in the string. This allows us to remove all duplicate occurrences while keeping only the last one ?

SELECT REPLACE_REGEXPR('(.)(?=.*\1)' IN '22331122' WITH '' OCCURRENCE ALL) FROM DUMMY

The output of the above code is ?

312

How the Regular Expression Works

Let's break down the regular expression pattern:

• (.) - Captures any single character
• (?=.*\1) - Positive lookahead that checks if the captured character appears again later in the string
• \1 - References the first captured group (the character)
• OCCURRENCE ALL - Applies the replacement to all matching patterns

This approach removes all multiple occurrences and keeps only the last occurrence of each character in the string.

Conclusion

Using REPLACE_REGEXPR with the pattern (.)(?=.*\1) effectively removes duplicate characters while preserving the last occurrence of each unique character in SAP HANA.