How to Match Email Column Values in AWS DMS Transformation Rule?

Hello,

I’m trying to create a transformation rule in AWS DMS to add a new column based on the email column’s value. Specifically, I want to check if the email starts with “sample”. Below is the JSON configuration I’m using:

{
  "rule-type": "transformation",
  "rule-id": "100",
  "rule-name": "100",
  "rule-action": "add-column",
  "rule-target": "column",
  "object-locator": {
    "schema-name": "public",
    "table-name": "users"
  },
  "value": "email_test",
  "expression": "CASE WHEN substr($email, 1, 6)=='sample' THEN 'match' ELSE 'unmatch' END",
  "data-type": {
    "type": "string",
    "length": 50
  }
}

However, the condition doesn’t seem to work as expected, and it’s returning ‘unmatch’ even when the email starts with “delete”. Am I using the correct syntax, or is there a better way to perform this kind of pattern matching in DMS? Is it possible to do something similar to a LIKE clause in this context?

Any insights or recommendations would be greatly appreciated!

Thank you.