Removing duplicates from string in QGIS

Question

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:

• Removing duplicates from field using ArcPy with ArcMap
• Removing duplicates in lines using PostGIS?
• Removing duplicate strings from field with Field Calculator in ArcMap

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).

Answer 1

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")))

Answer 2

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.