topic Re: Cutting out fields from a delimeted string in Qlik Replicate

Cutting out fields from a delimeted string

MoeE — Fri, 19 Jul 2024 02:11:13 GMT

Hi,

I have a column in the source table that is a delimited string. It has multiple fields and one of the fields in this string contains sensitive information that can't be sent to the target db. Is there a way to cut out one field from the string? Has anyone before faced this use case?

For example, a record from the source table contains this string

"AAAAA|BBBBB|CCCCC|DDDDD"

In the target we want it to appear like this

"AAAAA|CCCCC|DDDDD"

Regards,

Mohammed

Re: Cutting out fields from a delimeted string

john_wang — Fri, 19 Jul 2024 02:39:23 GMT

Hello Mohammed, @MoeE

Transformation can be used to cut part of the string. Would you please share what's the source and target DB types? We'd like to confirm the behavior for you.

BTW, (1) the fields are delimited by "|", is that correct? (2) It's used in Full Load only, or both Full Load and CDC.

Regards,

John.

Re: Cutting out fields from a delimeted string

DesmondWOO — Fri, 19 Jul 2024 10:35:06 GMT

Hi @MoeE ,

If you only want to remove the second field, please try this statement:

substr($C1, 1,instr($C1, '|')) ||
substr(substr($C1, instr($C1, '|')+1), instr(substr($C1, instr($C1, '|')+1), '|')+1)

Regards,
Desmond

Re: Cutting out fields from a delimeted string

Padma123 — Fri, 19 Jul 2024 11:01:53 GMT

Replace(string,Chr(124)&TextBetween(string,'|','|'),'')

Re: Cutting out fields from a delimeted string

DesmondWOO — Fri, 19 Jul 2024 11:26:22 GMT

Replace(string,Chr(124)&TextBetween(string,'|','|'),'')

Re: Cutting out fields from a delimeted string

MoeE — Mon, 22 Jul 2024 01:54:00 GMT

Hi Desmond,

Thanks this is what I was looking for. Have a good day.

Regards,

Mohammed