topic REPLACE(STR_VALUE, CHAR(13), '') in Qlik Replicate

REPLACE(STR_VALUE, CHAR(13), '')

RichJ — Mon, 05 Feb 2024 22:28:59 GMT

Hi Sir,

The replica source db is MS SQL server, the target is Snowflake db. Selection from the source db includes the statement like:

REPLACE(REPLACE(STR_VALUE, CHAR(13), ''), CHAR(10), '')

How to handle that in the qlik transform? Will simply copy 'REPLACE(REPLACE(STR_VALUE, CHAR(13), ''), CHAR(10), '')' in the Build Expression be good enough?

Thanks,

Richard

Re: REPLACE(STR_VALUE, CHAR(13), '')

john_wang — Tue, 06 Feb 2024 01:18:16 GMT

Hello @RichJ ,

Thanks for reaching out to Qlik Community!

There are many options to achieve this, the most easy way is using an Internal Parameter in target Snowflake endpoint:

replaceChars

Please check the detail information in article Qlik Replicate missing carriage returns in Snowflake.

Hope this helps.

John.

Re: REPLACE(STR_VALUE, CHAR(13), '')

DesmondWOO — Tue, 06 Feb 2024 02:23:20 GMT

Hi @RichJ ,

I wish to supply some additional information about the "replaceChars". For multiple replacements, please add pairs to the list. For example,

replaceChars=0x0a,0x20,0x0d,0x20

Regards,
Desmond

Re: REPLACE(STR_VALUE, CHAR(13), '')

Heinvandenheuvel — Tue, 06 Feb 2024 08:07:40 GMT

>>>Will simply copy 'REPLACE(REPLACE(STR_VALUE, CHAR(13), ''), CHAR(10), '')' in the Build Expression be good enough?

Yes, with the understanding that you need to pass in a variable with the leading $ sign: $STR_VALUE

When I work similar questions/solutions I always make sure to PARSE/TEST with SIMPLE values first like 65 and 66 for A and B and replace with X and Y first. Next try an empty string for one, and then two.

Once you see that work, you may assume that the non-printable CR/LF values 13/10 will also work.

the internal parameter replaceChars such as @DesmondWOO / @john_wang suggest is very good as well, but operates on every single target column.

Therefor if you need this for a single column or a a handful of columns then I'd stick to the REPLACE function for each of them. If it is needed for several columns/table and multiple tables, or if you are doing wildcard table matches and don't really know your columns to begin with, then replaceChars is a simple effective bruteforce solution.

Hein

Re: REPLACE(STR_VALUE, CHAR(13), '')

SushilKumar — Wed, 14 Feb 2024 06:21:05 GMT

Hello team,

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.

Regards,

Sushil Kumar

Re: REPLACE(STR_VALUE, CHAR(13), '')

gperedo — Tue, 23 Jul 2024 19:41:59 GMT

REPLACE(REPLACE(STR_VALUE, CHR(13), ''), CHR(10), '')