Parsing for numeric and character wildcards in a QGIS query

Question

In an attribute table with a land use shapefile in my QGIS project there are complex combinations of numeric and alphabetic character. I wish to filter for a simple combination of numeric wildcards and particular character combinations

A(35FDP_25MTR_15PST_10SDNze_10ARR_05VAP)

I want to filter the first set of codes 35FDP starting with A( including ddFDP for instance. I want to do the same for the second set of codes 15PST later.

My aim is to put the dd and FCP into two separate fields.

Any suggestions on how to use wildcards to parse these field number and text character combinations to set up new fields. It may take a number of goes to fill the new fields with numbers or text using different combinations of query filters.

Answer 1

It's not pretty but in QGIS3 you could do something like:

regexp_substr(
    array_get(
        string_to_array(
            substr('A(35FDP_25MTR_15PST_10SDNze_10ARR_05VAP)', 3, -1),
            '_'
        ),
        0
    ),
    '(\\d+)'
) = '35'
AND
regexp_substr(
    array_get(
        string_to_array(
            substr('A(35FDP_25MTR_15PST_10SDNze_10ARR_05VAP)', 3, -1),
            '_'
        ),
        0
    ),
    '([a-zA-Z]+)'
) = 'FDP'

Which is basically:

• Use substr to discard A( and )
• Use string_to_array splitting on _ to make an array of the land use codes
• Use array_get to get the item of interest from the array (0 is the first item)
• Use regexp_substr to extract either the character part or the numeric part

You may wish to coerce the numeric value into an numeric type so you could use mathematical comparison operators like < and > .

---

From a database design perspective you should really have a seperate table which is defined as (land_parcel_id, land_use_code, portion_percentage) and then for each parcel have as many rows is required in that table to represent the various land uses for a given land parcel.

You would then link these in QGIS by defining a relation.