Help in query having charndex/patindex

Question

Post reply

Help in query having charndex/patindex

sudhanva

Ten Centuries

Points: 1343
More actions
August 5, 2009 at 1:19 am

#203520

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 1

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

sudhanva Ten Centuries Points: 1343 More actions · Answer 2

sudhanva

Ten Centuries

Points: 1343

August 6, 2009 at 12:00 am

#1035191

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

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 3

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

sudhanva Ten Centuries Points: 1343 More actions · Answer 4

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

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 5

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