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:

enter image description here

  • All values include F or B or both, right? I mean "is there like: 5_45 or just 1?" Commented Dec 25, 2020 at 15:18

2 Answers 2


One of the solutions in a long way may be the following.

        string_to_array(linecode, '_'),
            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'
  • Thanks! A great solution.How learn to write expressions like this?It's fantastic
    – Ivan M
    Commented Dec 28, 2020 at 8:03

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'),

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

enter image description here

  • Thanks for solution and links!
    – Ivan M
    Commented Dec 28, 2020 at 8:04

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.