Extract numbers from mixed string (numbers & text) in QGIS

Question

In QGIS 3.18. There are several similar questions such as this one which do not address my exact problem.

I have an attribute of comma-concatenated values containing text and numbers. I would like to extract all of the numbers as they are in that list, retaining the commas -- or, said differently, remove everything that is not an integer, a period (as I wish to include decimal values) or a comma. (I then wish to retain only the largest of those numbers, but that might be a separate question.)

I have attempted variations of the expression in the above-linked question, regexp_substr("My data", '(\\d+)[^\\d]*$'), with the results in the table below; it seems decimals are not considered digits, and it doesn't seem to be taking more than one number in any case:

As an example :

My data	Desired output	Output with the above expression
1.50	1.50	50
0.30 (BRGM)	0.30	30
1.28 (bassin),1.53 (pré)	1.28,1.53	53
NR	NULL	NULL
NULL	NULL	NULL

Answer 1

I think something like ([\d.,]+)[^\d.,]* will work. I don't know how regexp_substr handles multiple capture groups so you might need to wrap that regexp in a custom function to get exactly your required output.

Answer 2

Following on from Ian's answer one way to get around multiple capturing groups is to convert your comma-separated attributes to an array (if they will always come in that format) with string_to_array(), run Ian's regexp_substr() expression on each item using array_foreach(), then convert the resulting array back to a string with array_to_string().

Something like

nullif(array_to_string(array_foreach(string_to_array("my_data",','), regexp_substr(@element,'([\\d.]+)[^\\d.]*')),','),'')

You can even perform additional functions on each array item with additional functions wrapped in the second parameter of array_foreach(), such as try(to_real()) to convert the text that results from the regex (e.g. '1.50') to an actual number (i.e. 1.50) and then perform mathematical operations. Or you can append additional text to each item in the array, etc.

---

Alternatively you can also use regexp_replace() to strip out all characters that are not digits, full stops or commas.

nullif(regexp_replace("my_data",'[^\\d.,]',''),'')