How to use regex to select rows where the column has more than two words in oracle

user_11734835 0
Viewed 64

for example:

id center
1 man
2 some men here

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

id center
2 some men here

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

oracle sql regex
3 Answers

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;
user_71100990

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

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

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
user_32463620
Related