CODEWITHSUNDEEP
regexmariadb
huey
huey

Reputation: 115

Extract last instance of substring from string

Here's my hash: 

---
0:
  id: 11259
  year: 1997
  status: other
  priority: 
  created_at: !ruby/object:ActiveSupport::TimeWithZone
    utc: 2017-01-13 15:02:22.000000000 Z
    zone: !ruby/object:ActiveSupport::TimeZone
      name: Etc/UTC
    time: 2017-01-13 15:02:22.000000000 Z
  updated_at: !ruby/object:ActiveSupport::TimeWithZone
    utc: 2017-01-13 15:02:22.000000000 Z
    zone: !ruby/object:ActiveSupport::TimeZone
      name: Etc/UTC
    time: 2017-01-13 15:02:22.000000000 Z
1:
  id: 82829
  year: 1931
  status: won
  priority: 
  created_at: !ruby/object:ActiveSupport::TimeWithZone
    utc: 2017-01-13 15:02:22.000000000 Z
    zone: !ruby/object:ActiveSupport::TimeZone
      name: Etc/UTC
    time: 2017-01-13 15:02:22.000000000 Z
  updated_at: !ruby/object:ActiveSupport::TimeWithZone
    utc: 2017-01-13 15:02:22.000000000 Z
    zone: !ruby/object:ActiveSupport::TimeZone
      name: Etc/UTC
    time: 2017-01-13 15:02:22.000000000 Z

I would like to do one of two things:

(1) Extract both 'id's

id: 11259
id: 82829

(2) Extract just the final 'id': 

id: 82829

So far I have only be able to extract the first ID: 

REGEXP_SUBSTR(hash_name, "id: .*?\n")

Thanks.

Upvotes: 0

Views: 163

Answers (2)

Se Br
Se Br

Reputation: 101

Try this user defined function which provide access to perl regex:

PREG_CAPTURE(pattern, subject [, capture-group] [, occurence])

EDIT:

PREG_CAPTURE("(?s)id:[^\n]+(?!.*id:)", hash_name)

Upvotes: 0

Barmar
Barmar

Reputation: 781503

If you're using a MariaDB version that uses PCRE, you can use a negative lookahead to exclude id: after the match.

REGEXP_SUBSTR(hash_name, '(?s)id:[^\n]+(?!.*id:)')

(?s) puts it into DOTALL mode so that .* matches across newlines. (?!.*id:) is a negative lookahead that disallows a match if it's followed by anything that includes id:. And id:[^\n]* matches id: followed by optional anything up to a newline.

DEMO

Upvotes: 1

Related Questions