Changing values​ ​in attribute table and split it into different columns?

Question

How to change the values ​ ​in the table and divide it into different columns? I have an example

I want to get a result

Answer 1

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 _ use: regexp_substr("text",'(\\d*)?')
• For the numbers after _ use: regexp_substr("text",'[_](\\d*)')

Result:

	text	before_	after_
1	16b_24f	16	24
2	16b	16	NULL
3	17b_23f	17	23

Explanation: (\\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 or 16b_24f_17c_18d into a list of comma separated numbers only, you could use something like

array_to_string(string_to_array(regexp_replace("text",'([A-Za-z])',''),'_'),',')

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.

Explanation: First regexp_replace() removes all letters from A-Z and 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 or 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.