Replacing characters using an expression in QGIS

Question

What regular expression can I use to change values in a string field? I need to change the letter B to a symbol - before the digits, and digits with letter F to make positive.
Like this:

Babel · Accepted Answer

If you want to use regular expressions, you can do this with the following expression in the field calculator - see the PCRE Regex Cheatsheet (that's the regular expression engine QGIS uses) [not reachable right now, see another site instead for the moment and let's hope the former site will be back soon] for functions and syntax.
This solution first converts your input to an array (each element delimited with _ is considered a separate value in the array), then with a for loop using array_foreach replaces the values: first, F is replaced with an empty value '', than from this output, it replaces any string at the beginning ^(.*), followed by B at the end of the string ($) with a - followed by the element at the first positon (1 - the cheatsheet above says: "Y Match the Y'th captured group" - however, in QGIS you have to use two  as mentioned in the context help of the expression editor) of the input:
array_to_string (
    array_foreach (
        string_to_array( "string", '_'),
        regexp_replace (
            regexp_replace(  @element, '^(.*)B$', '-1'),
            'F',
            ''
        )
    )
)

You can also create a virtual field to get dynamic updates when you insert new values in the first column:

Kadir Şahbaz · Answer

One of the solutions in a long way may be the following.
array_to_string(
    array_foreach(
        string_to_array(linecode, '_'),
        if(
            right(@element, 1) = 'F',
            substr(@element, 0, strpos(@element, 'F') - 1),
            '-' + substr(@element,0, strpos(@element, 'B') - 1)
        )
    )
)

How does it work:

split the value by underscore (_). -> ['6F', '45F', '29B']
check the last character for every element.
if it is F, return all characters except of the last one (F).
if it is not F (then the last character is B), then return all characters except of the last one (B) and add - to the beginning of element.  -> ['6', '45', '-29']
finally, convert the array into string.  -> '6,45,-29'

Replacing characters using an expression in QGIS

2 Answers

Add your own answers!

Ask a Question