如何计算Oracle中字符串中的单词数？

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

我正在尝试计算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.

我想显示单词的数量。在给定的示例中,尽管单词之间可能存在多个空格,但它将是3个单词。

4 个解决方案

#1

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

请参阅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 |

#2

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

由于您使用的是Oracle 11g,因此它更简单 -

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

Here is a sqlfiddle demo

这是一个sqlfiddle演示

#3

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.

由于我使用了双表,您可以直接在自己的开发环境中测试它。

#4

-1

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/

/从这个逻辑你可以继续alon /

#1