String Manipulation

[CarishmaM]

I have a column of 12 digit numbers, and I need to remove the last 6 digits of it.

IE :123456789123

I need it to be: 123456

I tried using substr($columnname$,-6)

I have tried using substra($columnname$, 0, $columnname$-6)

However, I keep receiving error messages. Does anyone have any alternative suggestions?

[ipazin]

Hello @CarishmaM,

you need a length() function. See here:

Remove n number of characters from the end of the strings in a column

Hello @deicide_bg, don’t think there is a need for such function as substring() function seems pretty straightforward: substr($col_name$, n) removes first n chars from string substr($col_name$, 0, length($col_name$) - n ) removes last n chars from string Hope this helps! Br, Ivan

Br,
Ivan

[bruno29a]

Hi @CarishmaM , if the length of the string varies, then you have to rely on the length() function as @ipazin suggested.

However, you mentioned that you are dealing with a column of 12 digit numbers. Are they always 12 digit? If that is the case, then your requirement can be rephrased to you want to keep the first 6 digits, which can be done simply by:
substr($columnname$, 0, 6)

[ipazin]

Hello,

made a mistake. length() function is needed when removing last n characters from string. For your @CarishmaM use case it’s not needed. Tnx @bruno29a.

Br,
Ivan

[bruno29a]

Hi @ipazin , you did not make a mistake. Your suggestion for length() actually is answering the request, which is to remove the last 6 digits, and might still be the solution to use if the column is not consistent with 12 digits. My solution works only if the column always has 12 digits.

So, you’re all good

[ipazin]

Indeed. Need a coffee . Tnx again
Ivan

[CarishmaM]

Hi,

I tried both options:

substr($MTN$, 0, 6)
substr($MTN$, 0, length($MTN$) - 6)

[bruno29a]

Hi @CarishmaM , can we see the node configuration?

You’re most probably not using the syntax of whichever node you are using properly

[CarishmaM]

I haven’t changed anything in the configuration. That’s why I mentioned the lines in the previous message. Here are screenshots:

[Daniel_Weikert]

@CarishmaM
Assuming you have always length of 12 right now
Try RegexReplace

regexReplace($column1$,"(\d{6}).*","$1")

should work for you
edit (use your column instead of my demo $column1$

[CarishmaM]

Hi Daniel,

I also tried that solution. However, I still receive an error message for Line 65 and Line 66. All of the cells in the column are 12 digits.However, I am not sure if the fact that Knime is reading the numbers as 2,222,222,222…

[Daniel_Weikert]

Sorry my mistake
try to use double backslashes

regexReplace($column1$,"(\\d{6}).*","$1")

Your regex is currently not recognized correctly
(Interesting I posted it with double backlash but that does only show \ so I need to post 3 backslashes to show 2 in the post ?)

[bruno29a]

Hi @CarishmaM , the substr works only with string columns, but your MTN column is a Double column, so you can cast your column to string in your manipulation, like this:
substr(string($MTN$), 0, 6)
or
substr(string($MTN$), 0, length(string($MTN$)) - 6)

Similarly for the regex that you are trying from @Daniel_Weikert 's suggestion, this would work on a string column only, so you have to cast your column to string there too if you want to use regex.

[CarishmaM]

Your first option worked perfectly. For future reference, can you explain what you mean by double column?

[bruno29a]

Sure @CarishmaM , when you look at the columns that you have, you notice that there is an icon in front of them:

That icon indicates the type of the column. For example:
[S] for String
[I] for Integer
[B] for Boolean
[D] for Double. Double is simply a type that can hold a number with decimal.

For example, 40 is an Integer and 40.0 or 40.00 is a Double.

[morpheus]

Hi @CarishmaM,
try substring$MTN$,0, $MTN$.length()-6).
If your data are integer or long data type you need to convert them to a string.

BR