August 5, 2009 at 1:19 am
Hi,
I need to get the number of hits of the specific set of words in a column.
Example:
DECLARE @document varchar(200)
SELECT @document = 'Reflectors are vital safety' +
' components of your bicycle .'
SELECT PatIndex('%vital%', @document)
SELECT CharIndex('vital', @document)
GO
here user input will be "safety" AND "bicycle" from the screen for search, i need to calculate the number of times these both words are repeated in a column.
Note: If any one word is present i should not count that. Both should exists.
Similarly user can input "safety" OR "bicycle".
Please help.
Thanks & Regards,
Sudhanva.
August 5, 2009 at 6:45 am
declare @table table (xtext varchar(100))
insert into @table
select 'bicycle safety' union all
select 'safety bicycle' union all
select 'bicycle sidewalk safety' union all
select 'safety goggles bicycle' union all
select 'bicycle highway speeding' union all
select 'motorcycle safety'
select * from @table
-- and logic
select count(*) from @table
where xtext like '%bicycle%' and xtext like '%safety%'
-- or logic
select count(*) from @table
where xtext like '%bicycle%' or xtext like '%safety%'
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 6, 2009 at 12:00 am
Hi,
Thanks for the reply.
I need a solution to get the count using patindex/charindex. I know how to achieve it using count(*).
And the number of words is not fixed.
Please help.
Thanks & Regards,
Sudhanva
August 6, 2009 at 12:05 pm
So you are saying that you can't use the solution because it doesn't use the patindex, charindex functions? This sounds like a homework assignment to me. What problems are you having when you try to do it? How are you approaching the problem? Have you even looked those functions up on books online?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 7, 2009 at 2:24 am
Bob Hovious (8/6/2009)
This sounds like a homework assignment to me. What problems are you having when you try to do it? How are you approaching the problem? Have you even looked those functions up on books online?
Yes I am very well aware of all function in the SQL. Thats not the point here.
My bad, that i didnt xplain the scenario in which i'm struck.
It is somethng like this:
I have to order/sort the search result based on the number of the word/phrase(which user enters) count in a particular column.
So for that i have written a function which accepts the columnname and searchText/userinput as parameters, and returns the count of the searchText in the corresponding Column.
------Code for FieldTextSearch UDF.
declare @Count int;
set @Count = 0;
While 1 = 1
Begin
Select @IDX = CharIndex(@SearchText, @FieldValue, @IDX + 1)
If @IDX = 0
Break
Else
Set @Count = @Count + 1
End
-----------------------
---------------------
return @Count;
-----------------------------------------------
My select query which calls the function looks like this:
select FirstName, LastName , dbo.FieldTextSearch('propofol',ColumnDescription)
from xyzTable
This will calculate the count of only one word, but i need a solution to calculate the count of multiple words, which has AND and OR conditions between them.
Hope u got the problem?
Thanks & Regards,
Sudhanva
August 7, 2009 at 10:09 am
I believe so, but I require further clarification. After rereading your initial post, let me give you the following examples and you tell me what the count should be for each:
1. Bicycle safety is an important topic. Is the count for this row 1 or 2?
2. Bicycle safety is important because your safety is at risk whenever you ride a bicycle. What is the count?
3. Bicycle safety is an often overlooked safety topic. What is the count?
Please tell me the counts you would expect using the AND option and also the counts you would expect using the OR option.
For future reference, if you provide concrete examples and expected results, others can visualize the problem much easier and test their code suggestions. You will also find that a lot more people attempt to help you when you provide sample table schema, and scripts to populate them. Please take the time to read the following article on best practices[/url] for setting up questions. Following its advice will benefit you greatly.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply