for example:

I want to select rows with three or more words so ouput should be:

I've tried using this: regexp_like(center, '\w{3,}') but it's not giving the expected output.

CodePudding user response：

You can use REGEXP_COUNT to look for more than 2 sets of words

WITH
    some_table (id, center)
    AS
        (SELECT 1, 'man' FROM DUAL
         UNION ALL
         SELECT 2, 'some men here' FROM DUAL)
SELECT *
  FROM some_table
 WHERE REGEXP_COUNT (center, '\w ') > 2;

CodePudding user response：

You could use the regex pattern \w \w \w :

SELECT id, center
FROM yourTable
WHERE REGEXP_LIKE(center, '\w [:space:] \w [:space:] \w );

CodePudding user response：

I think this is the regex you are looking for:

regexp_like(center, '((\s|^)\w (\s|$)?){3,}')

or with a short test:

select * from (
    select 'abc' center
    from dual
    union all
    select 'abc def'
    from dual
    union all
    select 'abc def ghi'
    from dual
    union all
    select 'abc def ghi jkl'
    from dual
) 
where regexp_like(center, '((\s|^)\w (\s|$)?){3,}')

It says

Start of line or whitespace
One or more letters
Whitespace or end of line, non-greedy
Repeat all of the above at least three times