CODEWITHSUNDEEP
sql-serverfull-text-search
Dale Fraser
Dale Fraser

Reputation: 4758

SQL Server 2014 Word Count

I have a SQL Server database that I'm searching for keywords.

All I need to know is how many of the keywords appear in a column.

For example

red,green,blue

If the column looked like this

I like red shoes but not blue

It would return the number 2

If the column looked like this

I like red shoes and green shoes, but green are my favourite

It would also return the number 2

2 of the keywords were found even though one was found twice.

Im open to using full text index or anything that is required, but Im stuck.

Upvotes: 0

Views: 115

Answers (4)

ps_prakash02
ps_prakash02

Reputation: 583

Try this

SELECT b.LineText, count(*) from 
FROM
(
select 'RED' Colors
union
select 'GREEN'
union
select 'BLUE'
) a
JOIN
(
SELECT 'I like red shoes but not blue' LineText
UNION
SELECT 'I like red shoes and green shoes, but green are my favourite'
) b ON b.LineText LIKE '%'+a.Colors+'%'
group by b.LineText

Upvotes: 1

Harish U
Harish U

Reputation: 89

This will work

     create table #temp(color varchar(500))
insert into #temp(color) values('red')
insert into #temp(color) values('green')
insert into #temp(color) values('blue')

Create table #temp2(string varchar(max))
insert into #temp2(string) values('I like red shoes and green shoes, but green are my favourite')
insert into #temp2(string) values('I like red shoes and green shoes')

select string,count(*) from #temp inner join #temp2 on PATINDEX('%'+color+'%',string)>0
group by string

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use a string splitter for this. Here is the DelimitedSplit8K function by Jeff Moden.

DECLARE @str VARCHAR(8000) = 'I like red shoes and green shoes, but green are my favourite'

SELECT
    COUNT(DISTINCT ITEM)
FROM dbo.DelimitedSplit8K(@str, ' ')
WHERE
    Item IN('red', 'green', 'blue')

SQL Fiddle

Upvotes: 1

sqluser
sqluser

Reputation: 5672

Try this one

WITH C(txt) AS(
  SELECT 'I like red shoes but not blue'
  UNION
  SELECT 'I like red shoes and green shoes, but green are my favourite'
)
SELECT COUNT(txt)
FROM C WHERE txt LIKE '%green%'
OR txt LIKE '%blue%'
OR txt LIKE '%red%'

Upvotes: 0

Related Questions