7

In QGIS 3.18.3, I have a GeoPackage attribute table containing a string field that I will call "FIELD_1". All records within "FIELD_1" take the form of a_a_a_a_a, where a = alphanumeric text of varying lengths.

The only consistent pattern within "FIELD_1" is that there are always exactly four underscores, each separated on both sides by the alphanumeric text.

I need to insert additional text (which I will call 'b') of fixed length immediately to the right of the fourth underscore, so that the output becomes a_a_a_a_ba.

I've reviewed the string functions in the field calculator, and I think that I need to do something like rpad(), but only after the fourth underscore. If that is the correct methodology, how do I identify the fourth underscore? Or is there a more generic approach to inserting strings in such cases?

1
8

You can use a regex

regexp_replace('aaa_bbb_ccc_ddd_eee','_([^_]*)$','*XYZ\\1')

==> aaa_bbb_ccc_ddd*XYZeee

let's break it down:

_([^_]*)$

_ find an underscore
() save what it finds within the parentheses as a capture group, that will be referred to as \\1
[^_] followed by any character that is not an underscore
* repeat several times the preceding instruction (i.e. get any char that is not an underscore)
$ until the end of the string

and we can replace this underscored followed by multiple non-underscore characters by whatever is wanted. Note that the underscore itself is removed (you can write it back, change the replacement part for '_XYZ\\1')

To make it work on the table's data, just replace the hard coded string by the desired column name

regexp_replace(the_column_name,'_([^_]*)$','*XYZ\\1')
3
  • This looks promising, but if I understand correctly, your solution assumes that the input string is literally 'aaa_bbb_ccc_ddd_eee'. However, I won't know the input string's content in advance - its contents will vary. How do I accommodate that unknown variability?
    – Stu Smith
    Jun 2 at 17:43
  • @StuSmith you can plug the column name instead of the hardcoded string regexp_replace(the_column_name,'_([^_]*)$','*XYZ\\1')
    – JGH
    Jun 2 at 18:00
  • 1
    Well duh, I knew that! :) Thanks for hanging in there for me! BTW, your solution worked great, and I learned a little about regex.
    – Stu Smith
    Jun 2 at 23:50
2

JGH's answer is great, but it assumes the string ends on the character you want to modify. If that's the case, you don't have to search the whole string and instead find the non-underscore characters at the end of the string:

([^_]*)$

This appears to be bugged in the regex_replace function in the field calculator (see this question).

If you need to search without anchoring to the back or front, you could use a positive lookbehind. This gets you the first non underscore character after the fourth underscore, which you can then change.

(?<=[^_]{1}_{1}[^_]{1}_{1}[^_]{1}_{1}[^_]{1}_{1})([^_]){1}

For a variable length lookbehind, use this pattern (although not all regex engines support variable length lookbehinds/aheads, though PostgreSQL does):

'(?<=[^_]*_{1}[^_]*_{1}[^_]*_{1}[^_]*_{1})([^_]){1}'

Regex testing link: https://regex101.com/r/6jiHAn/1/

Usage in the Field Calc:

regexp_replace('a_a_a_a_a','(?<=[^_]{1}_{1}[^_]{1}_{1}[^_]{1}_{1}[^_]{1}_{1})([^_]{1})','c\\1')

Gives 'a_a_a_a_ca'

enter image description here

7
  • Please include relevant code into your answer, since links are subject to change. Furthermore, please explain what you did and how it works. Thank you.
    – Erik
    Jun 2 at 15:51
  • I don't follow how you would use your example in the field calculator with regex. Also, why is there no closing parenthesis?
    – Stu Smith
    Jun 2 at 17:49
  • Looks like I found a strange bug in the regex_replace function in the Field Calc. Typing regexp_replace(pattern,'[^_]*$','c') gives 2 'c' letters at the end, and regexp_replace(pattern,'[^_]*$','c\\1') gives 'a_a_a_a_c\1c\1'. I'm going to explore this...
    – Encomium
    Jun 2 at 18:03
  • 1
    @Encomium since you have QGIS open, try my solution... it doesn't find the "last character" but the last underscore (and it prevents the duplication of the replacement string that you may be seeing now :-) )
    – JGH
    Jun 2 at 18:11
  • Yeah @JGH, yours works great (and I upvoted) :) I'm curious as to why regexp_replace('a_a_a_a_a','([^_])*$','c') gives 'a_a_a_a_cc' rather than 'a_a_a_a_c', but then I'm much more used to using regex in PostgreSQL or Python.
    – Encomium
    Jun 2 at 18:15
2

I try to stay away from Regex Voodoo if I can help it. I would use Python in the Function Editor. Much more readable in my opinion.

Enter this in the Function Editor tab of the Expression Calculator,

@qgsfunction(args='auto', group='Custom', )  #Necessary for QGIS to recognize the function
def addtext(original_text, insert_text, feature, parent):  
    index = original_text.rfind("_") + 1  #Find the last underscore and add one to the index
    output = original_text[:index] + insert_text + original_text[index:] # insert the text at the index
    return output

Then in the Expression Calculator use this

addtext(FIELD_1, "textYouWantToAdd")

Further documentation on the Function Editor can be found here.

2

Just because we can, how to solve your question using arrays:

enter image description here

The code:

with_variable('a', string_to_array( 'a_a_a_a_a', '_'),
  with_variable('a_len', array_length(@a), 
    with_variable('last', array_get(@a, @a_len-1), 
      with_variable('sliced', array_remove_at(@a, @a_len-1),
        array_to_string(array_append(@sliced, 'b' + @last), '_')
))))

Steps:

  1. Build the array from the original string.
  2. Get its length.
  3. Get the last element.
  4. Remove the last element.
  5. Add 'b' to the last element and append it to the sliced array.
  6. Convert to string.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.