Searching keyword in varcahr(max) column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Searching keyword in varcahr(max) column

Author

Topic

sreekanth939
Starting Member

12 Posts

Posted - 2009-12-16 : 06:59:56

hi frinds i have table with 2 columns...

Table name STORYTBL:
columns are storykey (primarykey),storytext(varchar(max))

let this be the one entry
storykey|storytext
1 |once upon a time a king and horse. king hit a man..king died

story starts and ends...and its 400 word story ...
wat my need is wen one user uses my "SEARCH" option with a key word "KING"....my search query will serach count of this word here"KING" and if the count of existance o "KING" is gretaer tan 3 in the one story it shud select....in the query...help me pls

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-16 : 07:03:51

select storykey,storytext from STORYTBL
where len(storytext)-len(replace(storytext,@search_value,''))>=3*len(@search_value)

Madhivanan

Failing to plan is Planning to fail

sreekanth939
Starting Member

12 Posts

Posted - 2009-12-16 : 07:18:23

quote:
Originally posted by madhivanan

select storykey,storytext from STORYTBL
where len(storytext)-len(replace(storytext,@search_value,''))>=3*len(@search_value)

Madhivanan

Failing to plan is Planning to fail

Cool..query tanks alot

one more question: can i select the exact count of tat word.....

for eg: 3

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-16 : 07:24:12

select storykey,storytext,(len(storytext)-len(replace(storytext,@search_value,'')))/4+1 as string_count from STORYTBL
where len(storytext)-len(replace(storytext,@search_value,''))>=3*len(@search_value)

Madhivanan

Failing to plan is Planning to fail

sreekanth939
Starting Member

12 Posts

Posted - 2009-12-16 : 07:42:54

quote:
Originally posted by madhivanan

select storykey,storytext,(len(storytext)-len(replace(storytext,@search_value,'')))/4+1 as string_count from STORYTBL
where len(storytext)-len(replace(storytext,@search_value,''))>=3*len(@search_value)

Madhivanan

Failing to plan is Planning to fail

thank u very much MADHIVANAN ....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-16 : 07:56:10

quote:
Originally posted by sreekanth939

quote:
Originally posted by madhivanan

select storykey,storytext,(len(storytext)-len(replace(storytext,@search_value,'')))/4+1 as string_count from STORYTBL
where len(storytext)-len(replace(storytext,@search_value,''))>=3*len(@search_value)

Madhivanan

Failing to plan is Planning to fail

thank u very much MADHIVANAN ....

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources