Splitting a string and extracting the numbers

Question

I'm using QGIS and i have an attribute field that contains several compacted info such as "aaa1-bbb2-ccc3" and i want to extract those three different numbers into new fields .. so far i have managed to do a half decent job in using RIGHT and LEFT functions and using the "-" as a reference but those need more steps into cleaning the data and I'm still left with the middle portion un handled

what is the cleanest way to have the columns filled with those numbers.

The data isn't always the same but i can easily clean set it with a simple REPLACE, the issue is when i have to extract the numbers themselves

for example i have one column that has data similar to this "aaa1-bbb2-ccc3" and the desired output would be three different columns, the first having "1", second "2" and the third "3"

Answer 1

You can achieve this, although it is not an elegant way, using regex_replace() function.

You can use the regex_replace() function to extract the numbers from the example you provided "aaa1-bbb2-ccc3":

Use update field to extract the first number using the following expression, and write it into the Field_1

 regexp_replace( "FieldName",'\\w+(\\d+)-\\w+(\\d+)-\\w+(\\d+)','\\1') 

To extract the second number and write it into the Field_2

 regexp_replace( "FieldName",'\\w+(\\d+)-\\w+(\\d+)-\\w+(\\d+)','\\2') 

To extract the thirst number and write it into the Field_3

 regexp_replace( "FieldName",'\\w+(\\d+)-\\w+(\\d+)-\\w+(\\d+)','\\3') 

Where "FieldName" is the field that hold the text with number. The one showed in your example.

You only need to change \\1 for first field to \\2 for the second, and to \\3 for the third field

Here is the output using the above expression:

Answer 2

This is more a workaround than a perfect solution so bear this in mind:

I propose a solution which uses the function editor and a bit of python. I will still need to specify the column you wish to update.

The function would be:

from qgis.core import *
from qgis.gui import *

@qgsfunction(args='auto', group='Custom')
def stringsplit_func(value1,val, feature, parent):
    int_lst = []
    for i in value1:
        try:
            if int(i):
                int_lst.append(int(i))
        except:
            pass            
    return int_lst[val]

Try except is to tackle the non-number values. The "val" is the number place you want to insert into the field:

Example:

field1 holds the value "aaa1-bbb42-ccc3" and you want to insert the 42 to the new field so you would update the existing field with stringsplit_func("field1", 1) and if you wanted the 1 you would write stringsplit_func("field1", 0) the number is the index of the list which starts at 0

be aware that if a row is empty this process would fail so you need some additional logic to tackle it