# RegexRouter transformation and regular expression in substitution string

**URL:** <https://forum.confluent.io/t/regexrouter-transformation-and-regular-expression-in-substitution-string/1570>\
**Category:** Kafka Connect\
**Created:** [23 April 2021 14:13 UTC](https://forum.confluent.io/t/regexrouter-transformation-and-regular-expression-in-substitution-string/1570 "2021-04-23T14:13:23Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![whatsupbros](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/whatsupbros/32/239_2.png) [@whatsupbros](https://forum.confluent.io/u/whatsupbros)\
**Post date:** [23 April 2021 14:13 UTC](https://forum.confluent.io/t/regexrouter-transformation-and-regular-expression-in-substitution-string/1570/1 "2021-04-23T14:13:23Z")

</div>

Hi Kafkateers!

Anybody extensively using [RegexRouter](https://docs.confluent.io/platform/current/connect/transforms/regexrouter.html) transformation here?

Does anybody know if this is possible to use some regex modifiers for the `replacement` string from the transofmation?

Let me describe the use-case in detail.

I have a sink JDBC connector, which takes multiple topics and writes the messages from them to multiple tables.

The connector configuration looks something like this:

```json
{
  "name": "sink-sandbox-revision-1",
  "config": {
    "connector.class": "io.confluent.connect.jdbc.JdbcSinkConnector",
    ...
    "dialect.name": "OracleDatabaseDialect",
    "topics": "source.organizations,source.departments,source.employees",
    "tasks.max": "1",
    "auto.create": "false",
    "auto.evolve": "false",
    "quote.sql.identifiers": "never",
    "transforms": "RouteRecords,HoistKey,ValueToJson",
    "transforms.RouteRecords.type":"org.apache.kafka.connect.transforms.RegexRouter",
    "transforms.RouteRecords.regex":"^source\\.(.+)$",
    "transforms.RouteRecords.replacement":"external_$1",
    "transforms.HoistKey.type": "org.apache.kafka.connect.transforms.HoistField$Key",
    "transforms.HoistKey.field": "RECORD_KEY",
    "transforms.ValueToJson.type": "com.github.cedelsb.kafka.connect.smt.Record2JsonStringConverter$Value",
    "transforms.ValueToJson.post.processing.to.xml" : "false",
    "transforms.ValueToJson.json.string.field.name" : "JSON_PAYLOAD",
    "insert.mode": "upsert",
    "pk.mode": "record_key",
    "pk.fields": "RECORD_KEY",
    "delete.enabled": "true",
    "errors.tolerance": "none"
  }
}

```

My target database is **Oracle** , and my tables are created as:

```sql
CREATE TABLE external_organizations (
  record_key VARCHAR2(255) NOT NULL,
  json_payload CLOB
  PRIMARY KEY (RECORD_KEY)
);

CREATE TABLE external_departments (
  record_key VARCHAR2(255) NOT NULL,
  json_payload CLOB
  PRIMARY KEY (RECORD_KEY)
);

CREATE TABLE external_employees (
  record_key VARCHAR2(255) NOT NULL,
  json_payload CLOB
  PRIMARY KEY (RECORD_KEY)
);

```

But when I deploy my connector, it fails, due to the [JDBC Sink connector bug](https://github.com/confluentinc/kafka-connect-jdbc/issues/1045). It seems to be an Oracle-related problem.

I found out that I can workaround this, by also having `"external_organizations"`, `"external_departments"` and `"external_employees"` tables in my db (with double quotes) - the error isn’t thrown then, however, the connector still uses the old three tables in such a case (the ones without quotes). All this looks really weird and I cannot rely on such things in production of course.

The other thing is, that when I use table names in UPPERCASE in the connector configuration, the pipeline works, and I thought _what if I convert the names to UPPERCASE in `RegexRoute` transformation? should be possible!_

So, I tried to use `"transforms.RouteRecords.replacement":"EXTERNAL_\U$1\E"` instead of `"transforms.RouteRecords.replacement":"external_$1"`, but unfortunately this didn’t work for me (though it fits to the regex substitution string format, and [usually works](https://regex101.com/r/95uEvn/1)).

Am I missing something? Or is there probably another way of how to convert target table names to UPPERCASE (please remember that I have multiple target tables and cannot simply use `table.name.format` config parameter for this)?

---

<div class="post-metadata">

**Author:** ![whatsupbros](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/whatsupbros/32/239_2.png) [@whatsupbros](https://forum.confluent.io/u/whatsupbros)\
**Post date:** [23 April 2021 14:14 UTC](https://forum.confluent.io/t/regexrouter-transformation-and-regular-expression-in-substitution-string/1570/2 "2021-04-23T14:14:13Z")

</div>

I googled a lot before posting my question, and it looks like [others also have similar issues](https://stackoverflow.com/questions/66900219/how-to-convert-table-name-to-uppercase-using-jdbc-sink-connector).

---

<div class="post-metadata">

**Author:** ![rmoff](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/rmoff/32/3_2.png) [@rmoff](https://forum.confluent.io/u/rmoff)\
**Post date:** [23 April 2021 14:20 UTC](https://forum.confluent.io/t/regexrouter-transformation-and-regular-expression-in-substitution-string/1570/3 "2021-04-23T14:20:09Z")

</div>

Check out this one: [ChangeTopicCase — Kafka Connect Connectors 1.0 documentation](https://jcustenborder.github.io/kafka-connect-documentation/projects/kafka-connect-transform-common/transformations/ChangeTopicCase.html)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex019/uploads/confluentcommunity/original/1X/c49438c90c9df282e9996fdf6971be890c71b65a.svg) [@system](https://forum.confluent.io/u/system)\
**Post date:** [23 May 2021 14:20 UTC](https://forum.confluent.io/t/regexrouter-transformation-and-regular-expression-in-substitution-string/1570/4 "2021-05-23T14:20:28Z")

</div>

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.