4

I'm trying to seperate characters from database to seperate fields. Database is like:

  • 999-10-1-1
  • 999-10-11-111
  • 999-10-111-1111
  • 999-10-22-1
  • 999-10-1111-2221

and I want something like in seperate fields:

  • 999 10 1 1
  • 999 10 11 111
  • 999 10 111 1111
  • 999 10 22 1
  • 999 10 1111 2221

Basically I want to pick numbers that are seperated by the (-) character.

9
  • 3
    You can do this with QGIS >= 3.4 (source) with a formula like string_to_array('999-10-1111-2221', '-')[0] for your first field, string_to_array('999-10-1111-2221', '-')[1] for second, etc. Jan 8 '20 at 15:55
  • So the number in brackets retrieves the corresponding array part? I don't see that documented in the manual or in the Expression Builder help.
    – csk
    Jan 8 '20 at 16:36
  • @csk: look at the link in my comment : docs.qgis.org/3.4/en/docs/user_manual/working_with_vector/… Jan 8 '20 at 16:39
  • Yes it works. Thank you.
    – woundari
    Jan 8 '20 at 16:47
  • @J.Monticolo You linked to the array functions section of the user manual, which lists the different array functions, but doesn't discuss the bracket format you used. Likewise, the examples in the Expression Builder help never use that format. And when I try to use the expression you provided, I get the error syntax error, unexpected Unknown_CHARACTER, expecting $end twice, referring to the brackets.
    – csk
    Jan 8 '20 at 16:49
2

In QGIS version 3.4 or greater, you can do this with a formula using the array functions. Create a new field to hold the first part of the database value (eg for the first value 999-10-1-1 this field will have 999) and populate it using the formula

string_to_array(FieldName, '-')[0] 

Create a second field for the next value, with

string_to_array(FieldName, '-')[1]

Create a third and fourth field in the same way, increasing the bracketed number by 1 each time.

Be sure to substitute the actual name of the field that has your database values where the example formulas where they say FieldName.

0

To change several fields at once, you can use the field calculator in batch mode (from processing toolbox). This is especially handy when you have a lot of fields to process, as you can use Autofill… to copy the same settings for all output fields. Add as many rows as you have new fields to create.

For the expression to use, you can define itself an expression (an expression-generated expression, so to say) with Calculate by Expression…. The expression is based on the comment by @J. Monticolo and the answer by @csk, but uses the variable @row_number instead for the index operator to batch create each element of the array in a new field.

enter image description here

Be aware, it's a bit tricky, it took me some time to understand, but once you got the principle, it's easy: in the expression builder of the batch window, you have access to the fields of the layer, but not to the variable @row_number. You have to use Calculate by Expression… instead to set an expression (string) that generates the final expression. In the Calculate by Expression… window, you have access to @row_number, but the fields are not available.

The trick is: insert in the Calculate by Expression… the expression as a (concatenated) string with single quotes: ' and be sure to mask the single quotes in the delimiter part by another single quote (resulting it two single quotes, not double quotes!). Use pipes || for String concatenation:

'string_to_array ("input", delimiter:=''-'' ) ['  ||  @row_number ||  ']'

When you close the dialog, this expression will be evaluated, resulting in

string_to_array ("input", delimiter:='-' ) [index]

with index as an auto-incrementing number from 0 to the last row number (as many as you added).

The output will be generated as a separate file for each field, unfortunately (did not check if there is another option). But by table join, you can re-join the newly created attributes back to your input layer. Again, this is especially efficient if you have a lot of fields to change as you could also use a batch process for the table join.

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.