CODEWITHSUNDEEP
sqlsql-serverdatabase
Chief
Chief

Reputation: 914

Search count of words within a string using SQL

Database: Sql Server

I have table column named page_text which contains following value

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

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

I should be able to search anything.

Upvotes: 5

Views: 8878

Answers (3)

Alex_L
Alex_L

Reputation: 2666

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

Upvotes: -1

memical
memical

Reputation: 2493

based on this code: 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.

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44316

declare @search varchar(10) = 'I'

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

Upvotes: 9

Related Questions