Regular expression to parse a string after second semi colon and before first hyphen

Question

I am writing a regular expression in SQL on Google Spanner. Following is the scenario:

I have a string ABC : DEP : 050-G&H Sample - IJ where I want to parse 50 from it.

Similarly, if I have a string ABC : DEP : PQ-Word1 Word2 Word3, I want to parse PQ from it.

I have followed two approaches to solve this:

Approach 1

I wrote a regular expression for it :([^:]*)-. This parses the above two examples in following way:

ABC : DEP : 050-G&H Sample - IJ - : 050-G&H Sample -

ABC : DEP : PQ-Word1 Word2 Word3 - : PQ-

Approach 2

I wrote :( )?([a-z]|[A-Z]|[0-9])*-, but the space in the parenthesis is ignored in Google Spanner, and it throws an error.

I want to achieve this using the first approach. Please provide some suggestions for it.

Answer 1

You may use

: *0*([^-:]+)-

See the regex demo

Details

• : - a colon
• * - 0 or more spaces
• 0* - zero or more 0 chars
• ([^-:]+) - Capturing group 1: one or more chars other than - and : (to only match alphanumeric chars, replace with ([a-zA-Z0-9]+))
• - - a hyphen