Get data between repeating asterisks in postgresql

Question

I need to get text between repeating asterisks in postgresql. String can be either like

"120 B-4, Gl-12 ***Test data****" Some other text" (with text between the asterisks)

or

*****(withoout text)

There can be two or more asterisks When there is text between asterisks result should be the text between the strings but when there is no text it should return empty string. There can be two or more asterisks and there might not be text between asterisks in that case result should be empty string. I was trying to achieve this by using regexp_split_to_array

regexp_split_to_array(o."Posts" , '([\*]{2,})'))[2]).

It works fine when there is text between the strings but when there is no data between the strings it fails.

Edit I think I am doing it the wrong way I need an expression which can match anything between asterisks other than asterisk

Your first example has only one asterisk making it hard to define 'between' in that context. — Chris Maurer
– Chris Maurer, Commented Jun 3, 2021 at 20:07
@ChrisMaurer I forgot to escape asterisks. I think I need an expression which can match anything between asterisks other than asterisk. — sqlcte
– sqlcte, Commented Jun 3, 2021 at 20:26
...and how to interpret your multiple *? I would try to return a 6 element array all blank except for the third element (return values for EACH consecutive pair of asterisks. — Chris Maurer
– Chris Maurer, Commented Jun 3, 2021 at 20:34

Ryszard Czech · Accepted Answer · 2021-06-03 20:42:18Z

The following might work for you:

SELECT regexp_matches(x, '[*]+([^*]+)[*]+', 'g');

See regex proof.

EXPLANATION

--------------------------------------------------------------------------------
  [*]+                     any character of: '*' (1 or more times
                           (matching the most amount possible))
--------------------------------------------------------------------------------
  (                        group and capture to \1:
--------------------------------------------------------------------------------
    [^*]+                    any character except: '*' (1 or more
                             times (matching the most amount
                             possible))
--------------------------------------------------------------------------------
  )                        end of \1
--------------------------------------------------------------------------------
  [*]+                     any character of: '*' (1 or more times
                           (matching the most amount possible))

Collectives™ on Stack Overflow

Get data between repeating asterisks in postgresql

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