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

2021-12-30 00:00:00 sql oracle11g oracle

我正在尝试计算 SQL 中一个字符串中有多少个单词.

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

Select  ("Hello To Oracle") from dual;

我想显示字数.在给定的示例中，尽管单词之间可能有多个空格，但它是 3 个单词.

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.

推荐答案

您可以使用类似的方法.这将获取字符串的长度，然后减去删除空格的字符串的长度.然后加上第一个应该给你的字数:

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

参见SQL Fiddle with Demo

如果您使用以下数据:

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
;

和查询:

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

结果是:

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

相关文章