I want to get a result
You can use regular expressions for this. As I understood, you want to extract the numbers before the
_ and after it and create two new fields out of it, where the second one should be null if there is no number after the
- For the numbers before
- For the numbers after
(\\d*) will match all numbers while
? indicates that there might be or not some other character after it.
[_] matches the
_ character, and
(\\d*) extracts all numbers after it, but only if there is a
_ char, so we get
NULL if there is none.
To turn e.g.
16b_24f_17c_18d into a list of comma separated numbers only, you could use something like
I am using arrays here and only one target column, so it is more universal and you can add as many
_12b_19x_09z_.... as you want. Otherwise you would need a new expression for every column, imagine having a string with houndreds of
12z_77a_..., its just easier that way.
regexp_replace() removes all letters from
a-z from your string, then a new array with all left values will be built, separated by
_. Finally we turn this array back into a comma separated string. Note that if you have more characters than
a-z, just add it inside the square brackets. You need to escape some special metacharacters like slashes. See this great wiki for more details.
You could also use something like
array_get(string_to_array(regexp_replace("text",'([A-Za-z])',''),'_'),10) to access a specific position of the array. Just replace the
10 with your desired position (starting with
0). Using this, you could fill all your columns using quite the same expression instead of getting a comma separated list.