CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Dheya Majid
Dheya Majid

Reputation: 413

How can I count the number of words in a string in Oracle?

I'm trying to count how many words there are in a string in SQL.

Select  ("Hello To Oracle") from dual;

I want to show the number of words. In the given example it would be 3 words though there could be more than one space between words.

Upvotes: 10

Views: 53265

Answers (4)

itzik Paz
itzik Paz

Reputation: 433

DECLARE @List       NVARCHAR(MAX) = '   ab a 
x'; /*Your column/Param*/
DECLARE @Delimiter  NVARCHAR(255) = ' ';/*space*/
DECLARE @WordsTable TABLE (Data VARCHAR(1000));

/*convert by XML the string to table*/
INSERT INTO @WordsTable(Data)
SELECT Data = y.i.value('(./text())[1]', 'VARCHAR(1000)')
FROM 
( 
SELECT x = CONVERT(XML, '<i>' 
    + REPLACE(@List, @Delimiter, '</i><i>') 
    + '</i>').query('.')
) AS a CROSS APPLY x.nodes('i') AS y(i)



/*Your total words*/
select count(*) NumberOfWords
from @WordsTable
where Data is not null;

/*words list*/
select *
from @WordsTable
where Data is not null

/from this Logic you can continue alon/

Upvotes: -1

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10063

If your requirement is to remove multiple spaces too, try this:

Select length('500  text Oracle Parkway Redwood Shores CA') - length(REGEXP_REPLACE('500  text Oracle Parkway Redwood Shores CA',
'( ){1,}', ''))  NumbofWords
from dual;

Since I have used the dual table you can test this directly in your own development environment.

Upvotes: 0

A.B.Cade
A.B.Cade

Reputation: 16905

Since you're using Oracle 11g it's even simpler-

select regexp_count(your_column, '[^ ]+') from your_table

Here is a sqlfiddle demo

Upvotes: 9

Taryn
Taryn

Reputation: 247690

You can use something similar to this. This gets the length of the string, then substracts the length of the string with the spaces removed. By then adding the number one to that should give you the number of words:

Select length(yourCol) - length(replace(yourcol, ' ', '')) + 1 NumbofWords
from yourtable

See SQL Fiddle with Demo

If you use the following data:

CREATE TABLE yourtable
    (yourCol varchar2(15))
;

INSERT ALL 
    INTO yourtable (yourCol)
         VALUES ('Hello To Oracle')
    INTO yourtable (yourCol)
         VALUES ('oneword')
    INTO yourtable (yourCol)
         VALUES ('two words')
SELECT * FROM dual
;

And the query:

Select yourcol,
  length(yourCol) - length(replace(yourcol, ' ', '')) + 1 NumbofWords
from yourtable

The result is:

|         YOURCOL | NUMBOFWORDS |
---------------------------------
| Hello To Oracle |           3 |
|         oneword |           1 |
|       two words |           2 |

Upvotes: 17

Related Questions