Compare strings, extract unique values from one (QGIS)

Question

In QGIS 3.18. I am looking for a field calculator expression able to return the values of a string (comma separated) in Field B which are not* also values of a string in Field A. For example:

• Field A: 2,4,6,8,10
• Field B: 1,2,3,4,5
• Field C (desired result of expression): 1,3,5

I suppose this will be a situation for string_to_array and then an array function, but array_distinct returns distinct values of one single array; perhaps there is a way to structure the expression such that it will compare the 2 cells, but I've not yet found it.

Answer 1

Use this expression:

array_to_string (
 array_remove_all(
 array_foreach (
 string_to_array(field_B),
 if (
 array_contains (string_to_array(field_A),@element),
 '',
 @element
 )
 ), 
 ''
 )
)

enter image description here

Answer 2

Great answer by @Babel!

Another option would be:

regexp_replace(replace("Field B", string_to_array("Field A"), ''), ',,', ',')

enter image description here

The core part of the expression is replace("Field B", string_to_array("Field A"), ''), but it will produce 1,,3,,5 so regexp_replace(~~~, ',,', ',') was added to remove doubled commas.

• Babel: I knew there was a simpler solution to that... great!
• Kazuhito: @Babel I believe your solution has much wider application than this!