Cutting out fields from a delimeted string

[MoeyE]

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

[john_wang]

Hello Mohammed, @MoeyE 

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.

[DesmondWOO · Accepted solution]

Hi @MoeyE ,

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

[Padma123]

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

[DesmondWOO]

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

It appears to be a Qlik Sense command. For Replicate, it supports SQLite syntax only.

Regards,
Desmond

[DesmondWOO]

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

[MoeyE]

Hi Desmond,

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

Regards,

Mohammed