6

In QGIS 3.18 in a shapefile attribute table, I have some fields with concatenated values (with commas , as the delimiter). Some of the values are duplicate, and I would like to remove them.

For example, if an attribute value is 37.3,37.8,44.A2,37.3,37.31, I'd like an expression that will catch the two instances of 37.3 and remove one of them (without removing the instance of 37.31 which is a different value).

I have not found a simple SQL expression that can do this in the Field calculator, though I am a relative beginner at SQL and Python.

On this site, there are similar questions:

but they are rather old, not for QGIS, and ever-so-slightly out of my league (perhaps the formulas are adaptable, but I'm not sure how).

7

Use this expression with the Field calculator to create a new field with duplicates deleted (only distinct values remain). "text" is the field name where the initial string is:

array_to_string(array_distinct(string_to_array("text")))

enter image description here

1
  • 1
    Simple and works for what I need. Thanks @Babel.
    – pete
    Sep 18 at 22:26
5

Since you mention SQL, here's a PostgreSQL approach to this issue:

WITH yourvalues AS (
    SELECT
        '37.3,37.8,44.A2,37.3,37.31' AS attributefield
    )

SELECT
    DISTINCT UNNEST(STRING_TO_ARRAY(attributefield,',')) AS unique_attributes
FROM
    yourvalues

Result:

unique_attributes 37.31 44.A2 37.8 37.3

Result without using DISTINCT:

attributes 37.3 37.8 44.A2 37.3 37.31

The yourvalues CTE doubles as the field in your table. The SELECT query first converts that attribute from a string to an array by using , as a delimiter, and then UNNESTs that array and selects DISTINCT (i.e. unique) values from the unnested array. You can use this kind of logic in any SQL variant, or the QGIS expression calculator.

2
  • 1
    You can use this syntax in either the DB Manager or the Expression calculator. But why not use a proper backend for your data?
    – Encomium
    Sep 18 at 22:04
  • Thank you @Encomium, I will investigate this possibility.
    – pete
    Sep 18 at 22:25

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.