A real hum dinger of an SQL problem.

DeVore

Ok I would consider myself a black belt in SQL if not quite a grand-master, but this one thing drives me nuts and I'm sure it can be done just not sure if I'll still be sane when finished.

I want to select a recordset where a free-text field contains a certain substring (say "blah" in "whatever blah whatever")

Ok so I use 'Where <fieldname> LIKE "%blah%" '

So far so hoopy.

Now but I want to sort the results by the NUMBER of instances of "blah" in the field.

ie: if the record has "blah" in it 10 times, it should come up higher then a record with "blah" in it only 9 times.

I'm sure it can be done, I just cant keep it straight in my head long enough to think it out.

There a pint or two in it for anyone who can solve it. Anyone?

DeVore.

Hobbes

You looking for the commands? Cause I don't know em.

But the code behind it is easy peesy.

Just search on the string for blah, increment counter then research for blah at location+length of blah (to stop substring/duplicates). If blah is > 10 then print record.

something like... (pseudo)

b$ = "blah"
r$ = record
counter = 0
x = instr(r$,b)
do while x > 0
  counter = counter + 1 
 if counter > 10 then
    print r$
    exit do
 end if
  x = instr(x+len(b),r$,b) 
loop

Quicker to do a strstr then for-next loop. I suppose you could create a temporary formula field which would create the count? so you would have a column with which to sort on?

Gerry

I'm not sure, but this might do something.

select <fieldname>, count(<fieldname> ) as NO_OF_OCCURENCES
from wherever
where <fieldname> like "%blah%"
group by NO_OF_OCCURENCES

probably completely wrong though!

kayos

ok heres an idea (I haven't time to work it fully out atm)
Create Table #Tmp
(
TextField VARCHAR(???),
Occur INT
)

Insert
#tmp
SELECT
FieldName,
1
FROM
Table
WHERE
FieldName LIKE '%Blab%'

Then Open up a cursor on #Tmp and use patindex to find the first instance of the seach string and then and then delete that instance and seach again etc look at yesterdays SQL problem for an example then just update the Occur field with the count of all the occurances and then select out ordering by this field. I'll try get a better answer at lunch when I have time.

Kayos

DeVore

Hobbes its a big database and speed is an issue, I can hand count them alright but I wanted a sleeker way to do it I guess... I may not have a choice...

Gerry, close but no cigar though at first I thought you were a genius

Kayos, thats hella ugly but I think it would work. Pretty gnarly way to do it, I thought SQL would have had a better way to order by relevance like this (given all the places that want to do it...)

In the end, we've indexed the files with Index Server which has relevance ordering built in. I'm still curious tho, cos that wont always work...

DeV.

kayos

Originally posted by DeVore:

Kayos, thats hella ugly but I think it would work. Pretty gnarly way to do it, I thought SQL would have had a better way to order by relevance like this (given all the places that want to do it...)

Hmmm ungly but the only pure SQL way that was posted Any who I was looking at SQL Books online and I cann't find any function that would do it straight off. I'll try to get time to post the answer here in a while just a bit up the walls ATM.

Kayos

Trojan

Hey Tom,

how about a Programming board, cos none of Tech, Web & Security would seem to fit the bill.

Whadya reckon?
Al.

Paladin

I have no help to offer, but I'm curious, what is a hum dinger and where did it originate?