A question of string manipulation

[qianyi]

Hi there!

I have a string column including file location and date&time information.
Now I need to convert this string like the following image.

Please notice the Date&time part of the last 3 rows contains different hour expression (2 digit).

I tried a lot to use Column Expressions Node to handle this manipulation but not successful.
Could you please give some advice to this question?

Here you can find the sample data.
StringConvert.xls (27 KB)

Thanks in advance.

[armingrudd]

Hi,

To get the format in “StringTo” column from the “String” column, use this expression in a String Manipulation node:
regexReplace($String$, ".*-", "")

If you want to use the Column Expressions node instead:
regexReplace(column("String"), ".*-", "")

Best,
Armin

[qianyi]

It worked! @armingrudd
Currently I don’t understand why it worked, need to study regex expression…

Thank you!

[armingrudd]

.*- selects all the string before the dash character and the dash itself and since there are multiple dash characters in the string, everything to the last one is selected. If you put a caret “^” at the beginning of the regex and the ? after * ( ^.*?-) then it only selects the first match which means everything before the first dash and the first dash itself will be selected.

@qianyi

need to study regex expression

Regular-Expressions.info - Regex Tutorial, Examples and Reference - Regexp...

[qianyi]

Thanks for your addition information. Completely understood!
I have also found some article on web for learning regex expression but it seems different in different tools . I will read the link from you in advance.

Many thanks!

[qianyi]

I have created the following regex expression in Column Rename (Regex) node:

(^.*_)

Target Column name is like: ABCD_EFG_001_00
The purpose is to match the string before the first “_”, so I consider ABCD should be selected.
But it doesn’t work ( all characters in the string has been selected).

Does Column Rename (Regex) node have different expression?

Thanks!

[armingrudd]

First, I’m sorry about a mistake in my previous post which I just edited.

You have to match the whole column name then if you want to use the first part (ABCD) in the replacing name, you should put the regex corresponding to the string in parentheses:
(^.*?)_.*

Now you can use $1 in replacement which stands for ABCD in this example.

P.S.

@qianyi

Does Column Rename (Regex) node have different expression?

Regex is the same in all nodes except when you are using \ to escape special characters. If you are using it in a expression inside e.g. a String Manipulation, you have to use another \ to escape the \.
But if you are using it in configuration settings, you don’t need to do that. That’s all.

[qianyi]

Understood! Thanks!