2

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

I want to get a result

enter image description here

3
  • Is this example exemplary for all your use-cases, or would the differ significantly? What have you tried so far?
    – Erik
    Dec 14, 2020 at 9:50
  • This is an old code of objects,i need to change them for new ones,removing the letters and _,then split the numbers into different columns.
    – Ivan M
    Dec 14, 2020 at 10:08
  • How many different codes are there?
    – Erik
    Dec 14, 2020 at 10:09

1 Answer 1

6

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.

4
  • Sufficient answer, but could you add some explanations? Especially how \\d+/* work.
    – Erik
    Dec 14, 2020 at 10:14
  • Which function should be in the case of 16b_24f_17c or 16b_24f_17c_18d?
    – Ivan M
    Dec 14, 2020 at 12:19
  • Each digit in its own column or in one column with a separator ','
    – Ivan M
    Dec 14, 2020 at 12:35
  • Thank you very much!!
    – Ivan M
    Dec 14, 2020 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.