Combining LIKE and CONTAINS operator in SAP HANA

In SAP HANA, you can combine LIKE and CONTAINS operators with regular expressions for advanced string manipulation. When removing duplicate characters from a string, using \1+ only removes consecutive occurrences. To remove all duplicate occurrences while keeping only the last occurrence of each character, you can use the REPLACE_REGEXPR function with a more sophisticated pattern.

Using REPLACE_REGEXPR with Regular Expressions

The pattern (.)(?=.*\1) works by capturing any character and using a positive lookahead to check if that same character appears later in the string. This allows you to remove all duplicate occurrences except the last one −

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

The output will be −

312

How the Pattern Works

The regular expression (.)(?=.*\1) breaks down as follows:

• (.) − 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
• OCCURRENCE ALL − Applies the replacement to all matches

This approach effectively removes all duplicate characters while preserving the last occurrence of each unique character, making it ideal for data deduplication scenarios in SAP HANA string processing.