Replace value in QGIS using field calculator

Question

How, through the field calculator, can I replace the value? But it should not change in two-digit and three-digit numbers. I need to replace '7' on '', but not 57. Value 7 can be anywhere in the line. I want the result be like 8,-18,41,57 (row number 2).

enter image description here

• TeddyTedTed: Can it occur multiple times in a single feature?
• Ivan M: Only with a different character before the number. Like 7,-7,57

Answer 1

You can achieve this using regular expressions.

regexp_replace(regexp_replace(LineCode,'(^|,)7(,|$)','\1円'),',$','')

(^|,): beginning of the string or a coma
7: followed by a 7
(,|$): followed by a coma or by the end of the string.

'\1円': replace by the 1st capture group, i.e. the beginning of the string or a coma.

It works fine but could leaves a trailing coma, so we wrap it again in another regex to find ,$ a coma followed by the end of the line, and we remove it.

PS: with some regex kung-fu it is very likely possible to put this in a single expression

• Ivan M: Thanks!!great solution!

Answer 2

This answer assumes that a value of '7' can only occur in a feature's "LineCode" attribute once.

So there are four cases that we have to accommodate when removing a value of '7' from the attribute. The first is when the attribute starts with '7', the second is when it ends with '7', the third is when '7' occurs anywhere in between, and the fourth is when an attribute contains only '7' and nothing else.

To do this we'll use a CASE function:

CASE
 WHEN "LineCode" = '7' THEN NULL --contains only '7' and nothing else
 WHEN LEFT("LineCode", 2) = '7,' THEN RIGHT("LineCode", LENGTH("LineCode")-2) --starts with '7'
 WHEN RIGHT("LineCode", 2) = ',7' THEN LEFT("LineCode", LENGTH("LineCode")-2) --ends with '7'
 ELSE REPLACE("LineCode", ',7,', ',') --has '7' in the middle somewhere
END

If contains only '7' and nothing else then we can just remove it, if it starts or ends with '7' then we can just truncate the string by two characters either at the start or end of the string, if it's in the middle then it will occur surrounded by commas so we include those commas in the REPLACE function and replace them all with a single comma.

• TeddyTedTed: Thanks @JGH, edited my answer to include that case