How do I Count the words in a string using regex

Question

I'm trying to count the words in a string using regex in Oracle 10g. I've been trying this

select *
from books
where REGEXP_LIKE(title, '[ ]{2}');

so that its returning titles with at least 3 words in the title.

10g is long gone. And Oracle has stopped supporting it long ago. Are you doing some client project or some non-serious one ? Because, you can download the freely available 12.2c and start working. — Ranagal
– Ranagal, Commented Sep 23, 2020 at 4:38
Oracle 12.2 is ending premier support soon. You should probably be planning your upgrade to 19.8. — William Robertson
– William Robertson, Commented Sep 23, 2020 at 6:47

EJ Egyed · Accepted Answer · 2020-09-22 18:51:55Z

2

INSTR is also a viable option. By looking for the second occurrence of a space, that will indicate that the string has at least 3 words.

WITH
    books
    AS
        (SELECT 'Tom Sawyer' title FROM DUAL
         UNION ALL
         SELECT 'A tale of two cities' FROM DUAL
         UNION ALL
         SELECT 'The Little Prince' FROM DUAL
         UNION ALL
         SELECT 'Don Quixote' FROM DUAL)
SELECT title
  FROM books
 WHERE instr(title, ' ', 1, 2) > 0;

If you do with to stick with regex, the regex expression below can be used to find books that have 3 or more words.

WITH
    books
    AS
        (SELECT 'Tom Sawyer' title FROM DUAL
         UNION ALL
         SELECT 'A tale of two cities' FROM DUAL
         UNION ALL
         SELECT 'The Little Prince' FROM DUAL
         UNION ALL
         SELECT 'Don Quixote' FROM DUAL)
SELECT title
  FROM books
 WHERE REGEXP_LIKE (title, '(\S+\s){2,}');

(Thanks @Littlefoot for the books!)

edited Sep 22, 2020 at 18:51

answered Sep 22, 2020 at 18:41

EJ Egyed

6,1341 gold badge11 silver badges25 bronze badges

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

2 Comments

jsuke06 Over a year ago

hey could you clarify the WHERE in the first block? WHERE instr(title, ' ', 1, 2) > 0; your indicating that you want all titles with a space to return, but what does the 1, 2 do?. How would you verbally say this function parameter? I'm just learning sql and I'm trying to understand everything as much as possible, thanks for your help

EJ Egyed Over a year ago

If you look at the documentation for INSTR (docs.oracle.com/cd/B19306_01/server.102/b14200/functions068.htm), the 1 represents the starting position in the string, and the 2 represents the occurrence. So INSTR(title, ' ', 1, 2) is looking for the second occurrence of a space from the 1st character in the string title.

Littlefoot · Accepted Answer · 2020-09-22 18:27:07Z

1

REPLACE does the job (with some calculation).

SQL> with books as
  2    (select 'Tom Sawyer' title      from dual union all
  3     select 'A tale of two cities'  from dual union all
  4     select 'The Little Prince'     from dual union all
  5     select 'Don Quixote'           from dual
  6    )
  7  select title
  8  from books
  9  where length(title) - length(replace(title, ' ', '')) >= 2;

TITLE
--------------------
A tale of two cities
The Little Prince

SQL>

answered Sep 22, 2020 at 18:27

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Comments

Ranagal · Accepted Answer · 2020-09-23 05:57:18Z

1

The below one is simple and easy to understand (works on 11g and later):

The below is just to create some sample data

create table books as
with tab as
(
    select 'Tom Sawyer' title from dual
    union all
    select 'A tale of two cities' from dual
    union all
    select 'The Little Prince' from dual
    union all
    select 'The_Little_Prince' from dual
    union all
    select 'Don Quixote' from dual
    union all
    select null from dual
)
select  title
from    tab;

The below is your solution to get those titles that have at least 3 words in it

select  title 
from    books
where   regexp_count(title, '\w+') > 2

Output:

edited Sep 23, 2020 at 5:57

answered Sep 23, 2020 at 4:51

Ranagal

3171 silver badge4 bronze badges

2 Comments

Littlefoot Over a year ago

Unfortunately, 10g doesn't understand REGEXP_COUNT so this won't work.

Ranagal Over a year ago

Oh, that is sad to hear. In fact, by the time started learning and coding, 10g was already gone. Anyway, I will edit my reply saying it works on 11g and later. That might help someone in the future having a similar scenario. Thanks for the correction.

Collectives™ on Stack Overflow

How do I Count the words in a string using regex

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related