Derived Column Error

Question

I have double pipes (||) in a CSV file, and I'd like to replace them with a single pipe (|) but I cannot do it.  Every-time I use the Derived column task to replace them it replaces the whole column with a single pipe. 

REPLACE( «character_expression», «search_expression», «replace_expression» )

Search expression is the Column name

Character expression is the ||

Replace expression is the |

the output is:

|

|

|

|

|

|

HELP!!!!!!!!!!!

Answer 1

Also the end of some of the rows will end in a pipe, and I want it removed. tblwbhbwhw|, ==> tblwbhbwhw,

How?????

Answer 2

Hello,

The Syntax to Replace inside a derived transofrmation is

REPLACE(COLNAME, "||", "|")

The Column Name is the First Parameter followed by the String To Replace, and finally the replacement string. Hope this helps

---

Regards, Dinesh

Answer 3

Hi,

As Dinesh has mentioned, use the REPLACE func as

replace (col.name,"||","|")

ie. replace 2 pipes with single. Please test and verify if this works.

---

Thanks, hsbal

Answer 4

Thanks!

Also if the column starts with | or ends with |  how do I remove it? A replace function with |, or ,| won't work.

Examples:

|NKRGL_RGL_ZZZ||NKRGL_NCM_ZZZ|
|NKRGL_RGL_ZZZ||NKRGL_NCM_ZZZ|
|GAEDU_SGA_ZZZ||GAEDU_GWN_ZZZ||GAEDU_DEK_ZZZ||GAEDU_FUL_ZZZ||GAEDU_CBB_ZZZ|

Answer 5

if the column within a CSV File starts with | or ends with |  how do I remove it?

A replace function with |, or ,| won't work.

Examples:

|NKRGL_RGL_ZZZ||NKRGL_NCM_ZZZ|
|NKRGL_RGL_ZZZ||NKRGL_NCM_ZZZ|
|GAEDU_SGA_ZZZ||GAEDU_GWN_ZZZ||GAEDU_DEK_ZZZ||GAEDU_FUL_ZZZ||GAEDU_CBB_ZZZ|

Answer 6

Hi, on the first column you can check the 1st character... 

(Substring(colname,1,1)=="|")?substring(colname,2,len(colname)-1):colname

And on the last column you can check the last character...

(Substring(colname,len(colname),1)=="|")?substring(colname,1,len(colname)-1):colname

... and strip off should it equal a pipe symnbol...

Answer 7

Add another Dervied Column after the original one to Replace (ColName, "|","") That will help

See image below.

---

Regards, Dinesh

How would you get rid of leading and ending pipes(|)?

Answer 8

Sorry about the earlier answer.

All you need is to include the Derived Transformation with the following code

Trim(Replace(ColName,"|",""))

This will replace the pipleines throughout your string. you do not need to do the replace twice. It does not matter if the pipeline is at the beginning or end the Replcae will search your colum and replace all | with an empty space.

---

Regards, Dinesh

Answer 9

Hi, on the first column you can check the 1st character... 

(Substring(colname,1,1)=="|")?substring(colname,2,len(colname)-1):colname

And on the last column you can check the last character...

(Substring(colname,len(colname),1)=="|")?substring(colname,1,len(colname)-1):colname

... and strip off should it equal a pipe symnbol...

Good job!!

The leading | disappeared but the ending | is till there!

Thanks anyway.

Answer 10

Sorry about the earlier Trim(Replace(ColName,"|",""))

This will replace the pipleines throughout your string. you do not need to do the replace twice. It does not matter if the pipeline is at the beginning or end the Replcae will search your colum and replace all | with an empty space.

---

Regards, Dinesh

I wanted to keep them throughout the whole string like so:

qfhwhvjh|hbvclhebqvlhb|lvhbadlhbvljh|abvabjbs

I only wanted to replace the double pipes and get rid of leading and ending pipes.

Answer 11

Hi, on the first column you can check the 1st character... 

(Substring(colname,1,1)=="|")?substring(colname,2,len(colname)-1):colname

And on the last column you can check the last character...

(Substring(colname,len(colname),1)=="|")?substring(colname,1,len(colname)-1):colname

... and strip off should it equal a pipe symnbol...

Does this allow for a reverse function to remove it that way?

Answer 12

Yep Dinesh... good one.