SQL 如何计算 Oracle 中字符串中的单词数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14008509/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I count the number of words in a string in Oracle?
提问by Dheya Majid
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 个单词。
回答by Taryn
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
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 |
回答by A.B.Cade
Since you're using Oracle 11g it's even simpler-
由于您使用的是 Oracle 11g，它甚至更简单-
select regexp_count(your_column, '[^ ]+') from your_table
回答by Mari
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 dualtable you can test this directly in your own development environment.
由于我已经使用了该dual表，因此您可以直接在自己的开发环境中进行测试。
回答by itzik Paz
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/
/从这个逻辑你可以继续单独/
