String Manipulation, multiple expressions

KNIME Analytics Platform
#1

Hi everyone
i’m new Knime user,
anyone can suggest me any effective nodes to solves this problem :
col1
216520000000004
216521300000006
2165277B1000003
2165212D1000007
216524200000004
etc

expected new col
65200
65213
65277B1
65212D1
65242

i used to solved this in excel with function
=IF(OR(MID($A1,8,1)=“B”,MID($A1,8,1)=“D”),MID($A1,3,7),MID($A1,3,5)).

tks

#2

Welcome to the forum, @Gilbert13.

For this, I would use the Column Expressions node. The following expression works if the column name is ColA :

if (or(substr(column("ColA"),7,1) == "B"), (substr(column("ColA"),7,1) == "D") )
    {
        substr(column("ColA"),2,7)
    }

else 
    {
    substr(column("ColA"),2,5)
    }

It’s basically the same as the Excel formula you have now, but KNIMEified:

  • use the substr function instead of MID
  • account for the fact that character indices start at 0 in KNIME
  • adjust format for the column expression node
2 Likes
#3

@elsamuel thank you for solution, but it appears only column that contains “D” extracted.

result
65200
65213
65277
65212D1
65242

the “B” does not work.
where am i wrong?

when i change the “D” first, the the result appears otherwise

#4

@elsamuel it works perfectly when i define it first,
thanks a lot, i appreciate it.

substr1 = (substr(column(“No Backsheet”), 7, 1) == “D”)
substr2 = (substr(column(“No Backsheet”), 7, 1) == “B”)
or1 = or(substr1, substr2)

if (or1)
{
substr(column(“No Backsheet”), 2, 7)
}
else
{
substr(column(“No Backsheet”), 2, 5)
}

1 Like
#5

Hello @Gilbert13,

alternatively you can use regexReplace() function available both in String Manipulation and already mentioned Column Expressions node. Here is expressions I used:

regexReplace($column1$,"..(\\d{5})([D|B].)?.*" ,"$1$2" )

Suggest to use the first one as should be faster.

Welcome to Community!

Br,
Ivan

1 Like
#6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.