Missing escape character

Four Stars

Missing escape character

I'm using the below statement in Talend, but it says that I'm missing an escape character. 

 

"update "EMS"."PUBLIC"."MAILCHIMP"
set LATITUDE = replace (LATITUDE,'\'','')"

 

 

 

Employee

Re: Missing escape character

Hi,

 

Could you please try like below?

 

"update EMS.PUBLIC.MAILCHIMP
set LATITUDE = replace (LATITUDE,'\'','')"

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Four Stars

Re: Missing escape character

When I tried it, it still errored out.

Four Stars

Re: Missing escape character

The problem is that the data in the field has a leading single quote.  That's the character I'm trying to eliminate.

Employee

Re: Missing escape character

Hi,

 

   Why don't you extract the data and do the replace in a tMap and push it back to table using primary key column?

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Employee

Re: Missing escape character

Hi,

 

   Please also try the below method specified in the post.

 

https://stackoverflow.com/questions/5800448/remove-single-quote-from-the-column-value-in-oracle-sql

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Four Stars

Re: Missing escape character

I can run the statement in the database, but not in Talend.  When I run it in Talend I have a double quote at the end and the beginning of course, but I get this error

 

[statistics] connecting to socket on port 4080
[statistics] connected
[ERROR]: org.talend.components.snowflake.runtime.SnowflakeRowStandalone - Query execution has failed. Please validate your query.
net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
parse error line 1 at position 78 near '<EOF>'.
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:135)
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:60)
at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:503)
at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:370)
at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:474)
at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:230)
at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:172)
at net.snowflake.client.core.SFStatement.execute(SFStatement.java:663)
at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:161)
at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQuery(SnowflakeStatementV1.java:92)
at org.talend.components.snowflake.runtime.SnowflakeRowStandalone.runAtDriver(SnowflakeRowStandalone.java:79)
at local_project.ems_pga_audience_copy_copy_copy_0_1.EMS_PGA_AUDIENCE_Copy_Copy_Copy.tDBRow_1Process(EMS_PGA_AUDIENCE_Copy_Copy_Copy.java:1152)
at local_project.ems_pga_audience_copy_copy_copy_0_1.EMS_PGA_AUDIENCE_Copy_Copy_Copy.tDBConnection_1Process(EMS_PGA_AUDIENCE_Copy_Copy_Copy.java:547)
at local_project.ems_pga_audience_copy_copy_copy_0_1.EMS_PGA_AUDIENCE_Copy_Copy_Copy.runJobInTOS(EMS_PGA_AUDIENCE_Copy_Copy_Copy.java:1513)
at local_project.ems_pga_audience_copy_copy_copy_0_1.EMS_PGA_AUDIENCE_Copy_Copy_Copy.main(EMS_PGA_AUDIENCE_Copy_Copy_Copy.java:1326)
[statistics] disconnected

Job EMS_PGA_AUDIENCE_Copy_Copy_Copy ended at 12:40 26/04/2019. [exit code=0]

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog