CODEWITHSUNDEEP
regexamazon-redshift
Miguel Santos
Miguel Santos

Reputation: 2016

Amazon redshift get numbers after string regex

From a public example we got this:

select email, regexp_replace( email, '@.*\\.(org|gov|com)$') 
from users limit 5; 

              email                | regexp_replace 
-----------------------------------+----------------  
  [email protected]  | DonecFri
  [email protected]            | mk1wait
  [email protected]           | sed
  [email protected]          | bunyung

In my case I have strings such as this one: 

ABCD01E002C
ABCSDD01E12C

And I want to retrieve the numbers between 1 and 9 after the E character So the output would be 2 and 12 respectively for the above example

Upvotes: 1

Views: 2203

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626738

You may use REGEXP_SUBSTR to match E and any zeros after it, and then capture a digit from 1 to 9 and any digits after it into a capturing group:

REGEXP_SUBSTR(email, 'E0*([1-9][0-9]*)', 1, 1, 'e')

The e argument tells REGEXP_SUBSTR to return the first captured value.

See the regex demo

Pattern details

  • E - a letter E
  • 0* - zero or more 0 chars
  • ([1-9][0-9]*) - capturing group #1:
    • [1-9] - a non-zero digit
    • [0-9]* - any 0 or more digits

Upvotes: 1

Related Questions