--- In firebird-support@yahoogroups.com, "Adam" <s3057043@y...>
wrote:

>
> --- In firebird-support@yahoogroups.com, "martinthrelly"
> <martinthrelly@y...> wrote:
> >
> > i am building a stored procedure to handle a database search. is
> there
> > any way i can take an input search string and create an array of
> > strings from it by parsing for a ' ' delimiter. then afterwards
> > dynamically create a variable for each item in the string array.
> >
> > example. say my SP receives the input search parameter "bloggs

joe

> > brisbane australia"
> >
> > somehow i want to dynamically create a variable
> > for "bloggs", "joe", "brisbane" and "australia".
> >
> > thanks
> >
>
> Not directly.
>
> It is possible to split the input parameter into each word using
> substring and a relatively simple while loop.
>
> You can certainly return a record from the stored procedure for

each

> of these values if thats what you mean.
>
> Output
> ======
> bloggs
> joe
> brisbane
> australia
>
> If you wanted to use these values in a query of some sort, you

could

> join to the stored procedure from another stored procedure, or use
> the execute statement syntax to build a dynamic query.
>
> But you need to give a bit more info about what you want to do

with

> those values.
>
> Adam
>

hi adam thanks for the reply.

i am building a denormalised table to facilitate a full text search.
i need an SP to query this table based on a search string which the
user submits. but i also want to order the results according to
ranking before returning any rows. so if a row contains both "joe"
and "bloggs" it will appear further up the results set.

the best way that i could see to tackle this problem was using 2
stored procedures.

SP_search would be reponsible for querying the datatable and adding
data to a dummy 'hits' integer column which will keep a tally of how
many hits there are (e.g. "joe" and "bloggs" equals 2 hits) this
kind of thing:

for
select * from searchindex where text containing :inputparam1 or text
containing :inputparam2 ...etc...
do
var i = 0;
if text containing :inputparam1
i = i + 1;
...etc...
hits = i;

then i would have a second procedure calling this which would order
by hits DESC. this would allow me to rank my results.


however, achieve this i think my search variables need to be input
parameters within an SP. of course there could be a much easier way
to achieve this that i havent thought about. your help is much
appreciated!