4

In QGIS, I have a string with the pattern (1 character)(1 character)(dot)(number with 1 or more digits).

How can I use regexp_replace() to get the number only?

I tried regexp_replace('ab.27',('(\\D\\D(.))(\d)'),('\2')), but output is '?27', where I want '27'.

How to mask the dot?

Edit

As I have a longer string then just what I presented here for sake of shortness as 'ab.27' and this longer string might also contain digits, I must be able to identify the pattern mentioned above.

So the strings might look as (-> for what I want to achieve):

354ab.27 -> 27
ab.27fg87 -> 27

2 Answers 2

8

You can search for any char (.*), followed by a dot (\\.), followed by a number that we capture ((\\d+)) followed by any char. Replace with the captured number.

regexp_replace('354ab.27xyz789','.*\\.(\\d+).*','\\1')

or you can search for a dot followed by a number that we capture

regexp_matches('354ab8.27xyz789','\\.(\\d+)')

Original answer for a pattern of (1 character)(1 character)(dot)(number with 1 or more digits):

Instead of searching for the exact pattern, you can search for what is not a number and replace it with an empty string:

regexp_replace('ab.27','[^0-9]','')

or you can look for numbers only

regexp_matches('ab.27','(\\d+)')

But if you are always having the same pattern, you can just take all chars but the first 3 ones:

right('ab.27',length('ab.27')-3)
3
  • 2
    The unfathomable depths of regular expressions.
    – Erik
    Commented Feb 9, 2022 at 14:07
  • 1
    :-) Specifically for QGIS as it is almost but not completely following the standards...
    – JGH
    Commented Feb 9, 2022 at 14:09
  • These three expressions work for the example provided, indeed. However, I have longer strings with digits before that - so I must find the pattern (char)(char)(dot)(digits). Will update the quesiton accordingly.
    – Babel
    Commented Feb 9, 2022 at 14:18
4

Base on your regular expression, you can use this:

regexp_replace('ab.27',('(\\D\\D\\.)'), ('')) returns 27.

ab.123456 returns 123456.

1
  • Indeed. However, I was not so clear in my initial question to make the issue a bit easier - see edit.
    – Babel
    Commented Feb 9, 2022 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.