|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
October 14th, 2003, 05:03 PM
|
|||
|
|||
|
how to count number of times a certain string appears in one field?
I'm building a basic search engine for a website and all of our content is stored in an Oracle Database. The content of each page in the site is stored in 1 field in a table ie. we do not have a column page_title, page_links, page_content. Rather all html for any one page is all in one field.
My problem is in ranking each page based on the number of times a certain string appears in the html for a page. I don't think the count function helps me here as it will only count if a string appears in a field, it will not count how many times a string appears in one field. Anyone have any ideas? Sorry if I'm not clear, I'm a little tired 
|
|
#2
October 14th, 2003, 10:48 PM
|
||||
|
||||
|
interesting challenge
here's my idea, i'll leave it to you to implement (i don't have oracle to test on) use the REPLACE function to replace every occurrence of the string with null this effectively removes the string from the field use the LENGTH function to get the length of the string, and also of the field, both before and after the string has been removed do the math and presto, the number of times the string occurred in the field beats looping in a scripting language, eh? rudy http://r937.com/ Last edited by r937 : October 14th, 2003 at 10:51 PM.
|
|
#3
October 14th, 2003, 10:48 PM
|
|||
|
|||
|
I can't think of a way to do that with simple sql.
I think you'd have to write your own function, it should be pretty trivial to do so but I'm not sure how fast it would be.
|
|
#4
October 14th, 2003, 10:52 PM
|
|||
|
|||
|
Quote:
Wow, that's a creative solution. I was thinking of using instr with a count and keep searching with the instring pointer after where I found it but I think your solution would be faster, and easier to code.
|
|
#5
October 14th, 2003, 11:01 PM
|
||||
|
||||
|
yes, something like
Code:
select (
length(field)
- length(replace(field,string))
)
/ length(string) as numberoftimes
from tablename
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > how to count number of times a certain string appears in one field? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
