how to remove trailing space and trailing tab (\t) from the end of a string

[RichJ]

Hi Sir,

When replicating a string column from a SQL Server table, we need to remove both trailing spaces and trailing tab. E.g.,  assuming "a bc     \t"  or  "a bc\t    "  in the source db, after replication, we want only "a bc" showing in the target db:  we need to remove both trailing space and trailing tab '/t', but keep the space and '/t' if they occur in the middle of the string.

I know RTRIM( ) will remove trailing spaces. So if we can substitute '/t' with space in both cases like "a bc     \n" and "a bc\n    ", then we are done.  If we use “Task Settings -> Character Substitution -> Add Character -> Enter the Unicode number for tab”, that will replace all '\t' in the string, but we need only remove trailing “/t”.

Is there a way to achieve that in Qlik replication?

Thanks,

Richard 

[Heinvandenheuvel]

Best would perhaps be to NOT have trailing whitespace in the source DB at all- but I suppose that's out of scope.

Second best I think is as you suggest to do a RTRIM(REPLACE ($<inputcolumn>,' ', ' '))

That third argument being a space, and the second argument would be a single quoted tab character. The Replicate GUI handles this just fine. You can verify with HEX(' ').

Of course for this to be a solution, the business folks have to agree that any tab anywhere in the string can be replaced by a space - for that column.

You could pass a quoted tab as argument to RTRIM, but that will bot be robust as it will not handle arbitrary combinations of spaces and tabs, unless you go crazy with 5+ RTRIM nested once with space once with tab, once again with space, once again with tab as deep as you see fit. - not good!

Hein.

[RichJ]

Removing all trailing spaces and tabs from  input_str is similar to the following regexp replace function:

     REGEXP_REPLACE(input_str, '[ \\t]+$', '')

So is there a way to implement the above function in Qlik replication with the source db as a MS SQL Server?

For example, the source string ‘abc/tabc/t   /t         /t    ‘  should be  ‘abc/tabc’ after the qlik replication (Note, it can look like ‘abc abc’ in the target db, but the middle space is actually ‘/t’).

Thanks,

Richard

[john_wang]

Hello @RichJ ,

I'm not sure what's the target endpoint in your scenario however the below expression works to remove the suffix TAB character (in the meanwhile keeping the prefix and middle TAB characters) :

removesuffix($notes,CHAR(09))

Hope this helps.

John.

[Heinvandenheuvel]

@john_wang - that works when there are only tabs, but not when there is a space following the tab . Yes you trim of that space first, but what if there is an other mix of tabs and spaces?

An actual tab in a quoted string also works but is not very clear, or rather too clear (see through). Char(09) is indeed a clean readable presentation of a tab character.

@RichJ  Any transformation function in Replicate must be provided by SQLite. By default there is no regex provided by SQLite and the mechanisme to provide one is not available through Replicate. There are a few more SQLlite functions available than documented, for example sqlite_version() is there (and useful, simply with parse - test) but you have to find them by trial and error using the documented SQLlite function list. hex (and listed under lob functions,  but 'unhex' is not there (too bad!).

https://www.sqlite.org/search?s=d&q=regexp

https://www.sqlite.org/lang_corefunc.html