3

Working in QGIS I have an attribute table containing details of polygons I have digitised, including the two fields, COLUMN_1 and COLUMN_2, each of which is a 4 character Integer that I entered.

However, having entered the last two characters of some entries in COLUMN_2 incorrectly, I now need to correct hundreds of records. Specifically the last two characters of the entry in COLUMN_2, for all COLUMN_1 records = 7777.

I could do this in a Spreadsheet but it's cumbersome and requires too many operations.

Opening the attribute table and starting the Field Calculator, I think the solution lies in the expression...

Case When 'COLUMN_1' = 7777 Then regexp_replace(input_string, regex, replacement)
End

or in my 'pseudocode' …

Case When 'COLUMN_1' = 7777 Then regexp_replace(“COLUMN_2”, right(contents of the field ,2), 99)
End

Am I working the right direction and if so how do I write this expression correctly?

The example below illustrates the problem.

From This... To this...

  • 1
    Please try CASE WHEN "COLUMN_1" == '7777' THEN replace(right("COLUMN_2", 2), '99') – Kazuhito Aug 19 '17 at 11:44
  • 1
    My apologies... the latter part should have been replace( "COLUMN_2" , right("COLUMN_2",2), '99'). – Kazuhito Aug 19 '17 at 12:28
  • 1
    Solved in an hour! Thank you Kazuhito. I used your revised code, getting the the same error. However, removing a single '=' produced the correct result. CASE WHEN "BC_NR_TYPE" = '7777' THEN replace("BC_NR_LU_F" , right("BC_NR_LU_F", 2), '99') End Brilliant, you have saved me hours. Many thanks! – Peter Francon Aug 19 '17 at 12:40
  • 1
    If this is solved could you add it as an answer and accept the answer so people can readily see the solution? – Ed Rollason Aug 19 '17 at 16:21
  • 1
    Thanks @Joseph , I wish I could... Honestly I had been noise at best. Will wait for Peter to post his solution when he has time. – Kazuhito Aug 21 '17 at 9:53
1

Apologies for my late reply, and by way of an answer to my problem...

Selecting only the records '7777's' from the Field (or Column): BC_NR_TYPE and changing the last two digits of Field: BC_NR_LU_F to '99'.

Using the String commands Replace(In Field, Chars from the right, with...) and Right(Chars from the rightmost Char)

Paste the statement below into the Field Calculator...

Case When 'BC_NR_TYPE' is '7777' Then replace ("BC_NR_LU_F",Right(2) '99') End

It is wise to first test your statement by Creating a Virtual Field, in the Calculator before ammending your actual data!

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.