Workaround for SQL Replace Function Execution

[AnkitaVi]

Hi,

I want to use the REPLACE function for one of my requirements, but since it is not available as a built-in function, I created a package and imported it worked. However, one of our clients is able to use the REPLACE function without having any package created, and it is not visible in their function list either. Despite this, it still works for them.

In another instance, when I try to use the REPLACE function, I encounter the following error:
“You are not authorized to use SQL Expression Replace.”

Could you please suggest an alternative workaround to execute the REPLACE function without importing a package? Your assistance would be greatly appreciated.

Thank you!
Ankita Vishe

[SushantTrimukheD · Accepted solution]

Hi @AnkitaVi ,

Why It Works for Your Client Without a Package:

Your client’s instance likely has a configuration that allows unrecognized SQL functions to be passed directly to the underlying database engine (e.g., PostgreSQL, SQL Server, etc.). This is controlled by the "XtkPassUnknownSQLFunctionsToRDBMS" option in Adobe Campaign’s configuration. When this option is enabled (set to 1), Campaign passes unknown functions like REPLACE to the database, which executes them natively if supported. This would explain why it works for them without explicitly defining it in a package.

In your instance, this option is likely disabled (set to 0), causing Campaign to block the use of REPLACE and throw the authorization error.

Let me know if you are able to check this option in the client where it is working and at your another instance where this is not working.
Otherwise we can use JavaScript in a Workflow with the below example.

Example:

var inputString = "Hello World";
var newString = inputString.replace("World", "Campaign"); // Replaces "World" with "Campaign"
sqlExec("UPDATE yourTable SET yourColumn = '" + newString + "' WHERE condition");

• Replace yourTable, yourColumn, and condition with your actual table, column, and filtering logic.
• The .replace() method in JavaScript mimics SQL’s REPLACE functionality.
• This avoids direct SQL function calls, bypassing the authorization error.

Thanks

Sushant Trimukhe

[AnkitaRa2]

Thanks a lot, Sushant! This was helpful. I cross-checked with another client, and the option is set to 1, which is why it was working. It worked on my end as well.