Regex to get unique values in field using QGIS field calculator

Question

I need to get the unique values from a field.

(?<=,|^)([^,]*)(,1円)+(?=,|$)

Works in an online test but I get invalid expression in QGIS Field Calculator. How can I rewrite this in QGIS Regex implementation or is there another maybe pythonic way to get the same result? In the end it needs to work in QGIS Modeller.

regexp_matches( "Locality_L","(?<=,|^)([^,]*)(,1円)+(?=,|$)")

enter image description here

Update 2 As per @she_weeds suggestion I tried with '' around the regex statement, it runs but there's no value in the table even though a sample result is shown in the field calc pane. enter image description here

• she_weeds: The second parameter in regexp_matches (the actual regex) is a string literal and therefore requires single quote marks ('), not double quotes (") which is interpreted as a column reference (hence the error)
• GeorgeC: @she_weeds it runs now but no values are shown in the table. See updated question.

Answer 1

Instead of using regex_match you can define a new function and use that custom function to extract the unique text without using regular expression.

You can use the following function to do the job:

from qgis.core import *
from qgis.gui import *
@qgsfunction(args='auto', group='Custom')
def unique_text(text_field, feature, parent):
 split_text = text_field.split(',')
 lower = [txt.lower().strip() for txt in split_text]
 uniqueText = set(lower)
 joinUnique = (",".join(uniqueText)).title()
 return joinUnique

Using Field Calculator -> go to Function Editor and create a new expression and give it a name, in this example ExtractUniqueText

enter image description here

Under the Field Calculator -> Custom use Unique_text which is the name of the defined function with the field name that you want to extract the unique text from.

enter image description here

Here is the output:

enter image description here

• spatialthoughts: Thanks! Custom function works well. unfortunately array_distinct() function in QGIS works only on numbers, otherwise that would have been an ideal solution.
• ahmadhanb: @spatialthoughts I agree with you. array_distinct() would be excellent if it works with text also.

Answer 2

Because you used regexp_matches() instead of regexp_match() it will return an array (notice the square brackets in Output preview at the bottom of your field calc screenshot) as opposed to a string. You need to convert the array to a string by wrapping your expression in array_to_string().

You also need to escape any backslashes so 1円 needs to be \1円.

It appears that there some further tweaks you will need to make to your regex; different dialects behave differently - I would recommend testing against the Python flavour.