Extracting two last characters (numbers) doesn't work properly in QGIS

Question

I have the string CBG/FON/BCK-STO/A1/FDJ10 and I would like to extract the 2 last numbers (sometimes one, sometimes three).

I found some hint here: Splitting string (the last digits) in QGIS

but it doesn't work as expected.

The following formula:

regexp_substr("Field_Name", '(\\d+|\\d+.+)')

returns only 1 regardless of the number at the end.

Where can I find the principles for deduction the certain number of string or numbers from this string?

This thread Extracting only number from address in QGIS Attribute Table also doesn't work.

score 9 · Answer 1 · answered Feb 21 '22 at 13:53

9

You can use the pattern (\\d+)$, which will look for 1 or more number followed by the end of the line.

If you omit the $, the first occurence of one or more number will be returned, i.e. the 1 of A1.

regexp_substr('CBG/FON/BCK-STO/A1/FDJ10','(\\d+)$')
-->10

answered Feb 21 '22 at 13:53

JGH

41,794
3
43
89

Yes, but the pattern will change from i.e. FDJ10 to FDJ12, FDJ1, and so forth. How about this situation? – Geographos Feb 21 '22 at 14:03
1

This pattern will always return the ending numbers (10,12,1 from your example). – JGH Feb 21 '22 at 14:05

score 6 · Answer 2 · answered Feb 21 '22 at 17:46

You can use:

regexp_substr( right("Field_Name", 3), '(\\d+)')

Examples:

regexp_substr( right( 'CBG/FON/BCK-STO/A1/FDJ1', 3), '(\\d+)') -- 1
regexp_substr( right( 'CBG/FON/BCK-STO/A1/FDJ12', 3), '(\\d+)') -- 12
regexp_substr( right( 'CBG/FON/BCK-STO/A1/FDJ123', 3), '(\\d+)') -- 123

score 5 · Answer 3 · answered Feb 21 '22 at 14:10

5

Assuming the 2 to 3 digits are only located in the last part, you can try the following expression:

regexp_substr("Field_Name",'\\d{2,3}')

Here is the result:

answered Feb 21 '22 at 14:10

ahmadhanb

40,826
5
51
105

Extracting two last characters (numbers) doesn't work properly in QGIS

3 Answers3