GoldenGate @STRRTRIM function example

Here’s a complex example of using the STRRTRIM function in Oracle GoldenGate. This example demonstrates removing multiple different trailing characters, using them inside a CASE statement, and applying them conditionally. For those unfamiliar, STRRTRIM is a powerful string manipulation tool.

Scenario:

You are replicating data from a customer orders table, where some customer names in the source database have unwanted trailing characters, such as #, *, or spaces. You want to clean up these names before inserting them into the target database.

Example Data (Before Transformation)

CUSTOMER_ID	CUSTOMER_NAME
101	John Doe###
102	Alice Smith*
103	Bob Johnson
104	Charlie Brown##

GoldenGate Mapping Using `STRRTRIM`

TABLE MAP SOURCE.CUSTOMERS, TARGET TARGET.CUSTOMERS,
    COLMAP (
        CUSTOMER_ID = CUSTOMER_ID,
        CUSTOMER_NAME = STRRTRIM(STRRTRIM(STRRTRIM(CUSTOMER_NAME, '#'), '*'), ' ')
    );

Explanation:

First STRRTRIM(CUSTOMER_NAME, ‘#’) → Removes any trailing # characters.
Second STRRTRIM(…, ‘*’) → Removes any trailing * characters.
Third STRRTRIM(…, ‘ ‘) → Ensures that trailing spaces are removed.
Final cleaned CUSTOMER_NAME is mapped to the target table.

Example Data (After Transformation in Target Table)

CUSTOMER_ID	TARGET.CUSTOMER_NAME
101	John Doe
102	Alice Smith
103	Bob Johnson
104	Charlie Brown

Adding Conditional Logic with `CASE` Statement

In some cases, you might want to apply STRRTRIM only if the customer name contains certain characters. Here’s how you can do it inside a CASE statement:

TABLE MAP SOURCE.CUSTOMERS, TARGET TARGET.CUSTOMERS,
    COLMAP (
        CUSTOMER_ID = CUSTOMER_ID,
        CUSTOMER_NAME = CASE 
            WHEN CUSTOMER_NAME LIKE '%#' THEN STRRTRIM(CUSTOMER_NAME, '#')
            WHEN CUSTOMER_NAME LIKE '%*' THEN STRRTRIM(CUSTOMER_NAME, '*')
            ELSE CUSTOMER_NAME
        END
    );

Key Takeaways:

STRRTRIM helps remove trailing characters from a string.
You can nest multiple STRRTRIM calls to remove different trailing characters.
It can be used inside CASE statements for conditional transformations.

Bonus Tip: If you need to remove leading and trailing characters, you can use STRLTRIM (for leading) and STRRTRIM (for trailing) together:

STRLTRIM(STRRTRIM(CUSTOMER_NAME, '#'), '#')

This removes # characters from both the beginning and end of the string.

Alex Lima