postgres substring split text using regex

Question

I am having following string pattern and I want to split the text into 4 fields.

NIFTY21JUN11100CE --> NIFTY, 21JUN, 11100, CE

In above string, only 2 string formats are constant. For ex: 21JUN represents year and month and it is constant 5 character representation. Before that represent name which can be any number of characters. I think regex will be like (([1-2][0-9]))(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)

last 2 characters are constant and its value can be either PE|CE. value between 21JUN and CE|PE represent strike price and it is always numeric but can be any number of digits.

Now I want them to be split into 4 fields and struggling to get the regex. Is anyone familiar with Postgres command for this requirement?

` SELECT regexp_match('NIFTY21JUN11100CE','^(\D+)(\d{2})(\w{3})(\d+)(PE|CE)$');` — clamp
– clamp, Commented Jan 31, 2021 at 0:04
@clamp I think your RE is a littler aggressive as it returns 5 fields instead of 4. The section "(\d{2})(\w{3})" should just be (\d{2}\w{3}). — Belayer
– Belayer, Commented Feb 1, 2021 at 0:49
Thanks a lot for your help Clamp and Belayer. Yes, I was able to get that into 4 fields into array and use them further in the query. Cheers. — acul
– acul, Commented Feb 1, 2021 at 1:04

clamp · Accepted Answer · 2021-02-01 10:33:55Z

1

You can use SELECT regexp_match('NIFTY21JUN11100CE','^(\D+)(\d{2}[A-Z]{3})(\d+)(PE|CE)$');

Step by step:

^          Beginning of the string
(          start capture
\D+        more than zero non-digit chars
)          end capture
(          start capture
\d{2}      exactly 2 digits
[A-Z]{3}   exactly 3 chars in the range from A to Z
)          end capture
(          start capture
\d+        more than zero digit chars
)          end capture
(          start capture
PE|CE      one of 'PE' or 'CE'
)          end capture
$          end of the string

The year-month regexes from your question using character classes [1-2][0-9] and alternations (JAN|FEB|...) are a little bit more strict and could also be used.

answered Feb 1, 2021 at 10:33

clamp

3,6011 gold badge10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres substring split text using regex

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related