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:

enter image description here

• Kadir Şahbaz: All values include F or B or both, right? I mean "is there like: 5_45 or just 1?"

Answer 1

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'

• Ivan M: Thanks! A great solution.How learn to write expressions like this?It's fantastic

Answer 2

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:

enter image description here

• Ivan M: Thanks for solution and links!