Searching only for specific words in string excluding anything in between

Question

I am looking for a pattern which can extract only the search words given in pattern and exclude any other words in between.

So for the input string...

2637:1888 log :[INFO] :    create    /* some comment*/    table mytab (n numeric)

... I need to extract 'create' and 'table'.

If anything else is in between create and table word I should be getting null. I tried with various combination but I am not able to get much clue

If there is no comment in between create and table word then my pattern works i.e

(create)\s*(table)

What should I do?

First, remove the comments with `regexp_replace(col, '/\*[^*]*\*+(?:[^/*][^*]*\*+)*/', ' ')`, then run your regex. — Wiktor Stribiżew, Sep 20 '19 at 11:23
@konardrudolph : Thanks for your reply to my query. I tried regexp_replace to my initial thought where I need to replace multiple spaces with single space between "create" and "table", which worked well. But later my requirement changed where I need to exclude any comment in between "create" and "table" word, for which you provided the pattern. I am not sure if there is a way where we can exclude regexp_replace function and get the required output with single pattern for the same output. May be I am getting crazy.... — user12095154, Sep 22 '19 at 20:01

score 0 · Answer 1 · answered Sep 22 '19 at 20:15

You may use

where col ~ '\ycreate(?:\s*/\*[^*]*\*+(?:[^/*][^*]*\*+)*/)*\s*table\y'

See the regex demo (\y replaced with PCRE \b for the demo purposes).

The pattern matches:

\y - a word boundary
create - a string create
(?:\s*/\*[^*]*\*+(?:[^/*][^*]*\*+)*/)* - zero or more occurrences of
- \s* - 0+ whitespaces
- /\*[^*]*\*+(?:[^/*][^*]*\*+)*/ - a multiline C comment pattern
\s* - 0+ whitespaces
table - a string table
\y - a word boundary

See the online demo:

CREATE TABLE tabl1
    (test character varying)
;

INSERT INTO tabl1
    (test)
VALUES
    ('1: 2637:1888 log :[INFO] :    create  table mytab (n numeric)'),
    ('2: 2637:1888 log :[INFO] :    create    /* some comment*/  table mytab (n numeric)'),
    ('3: 2637:1888 log :[INFO] :    create    /* some comment*/   /**//* some comment2*/    table mytab (n numeric)'),
    ('4: 2637:1888 log :[INFO] :    create    WOW  table mytab (n numeric)')
;

select * from tabl1 where test ~ '\ycreate(?:\s*/\*[^*]*\*+(?:[^/*][^*]*\*+)*/)*\s*table\y';

Output:

Searching only for specific words in string excluding anything in between

1 Answers1