使用SQL搜索字符串中的单词数

Database: Sql Server

数据库：Sql Server

I have table column named page_text which contains following value

我有名为page_text的表列，其中包含以下值

" I Love *.com because i can post questions and get answers at any time. "

“我喜欢*.com，因为我可以随时发布问题并获得答案。”

Using SQLQUERY i want to search the number of I it has . So in this string it would should return 4.

使用SQLQUERY我想搜索它的数量。所以在这个字符串中它应该返回4。

I should be able to search anything.

我应该可以搜索任何东西。

3 个解决方案

#1

declare @search varchar(10) = 'I'

select len(replace(PageText, @search, @search + '#')) 
- len(PageText) as Count from YourTable

#2

based on this code: http://www.sql-server-helper.com/functions/count-character.aspx

基于此代码：http：//www.sql-server-helper.com/functions/count-character.aspx

you create a function:

你创建一个函数：

CREATE FUNCTION [dbo].[ufn_CountSpecificWords] ( @pInput VARCHAR(1000), @pWord VARCHAR(1000) )
RETURNS INT
BEGIN

RETURN (LEN(@pInput) - LEN(REPLACE(@pInput, ' ' + @pWord + ' ', '')))

END
GO

this however implies that you save your strings with a leading and trailing space and you replace any other separators like ',' and '.' with spaces.

然而，这意味着您使用前导和尾随空格保存字符串，并替换任何其他分隔符，如'，'和'。'有空格。

and you need to refrase your question if you want the count of words or just the appeareance of a word.

如果你想要单词的数量或只是单词的外观，你需要重新提出你的问题。

#3

-1

SELECT (LEN(@Text) - LEN(REPLACE(@Text,@SearchString,''))/(Len(@SearchString))

#1

declare @search varchar(10) = 'I'

select len(replace(PageText, @search, @search + '#')) 
- len(PageText) as Count from YourTable

#2