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

[RichJ]

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

[john_wang]

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.

[DesmondWOO]

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

[Heinvandenheuvel]

>>>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

[SushilKumar]

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

[gperedo]

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