UDF for MSSQL full-text searches

Hi, I am using the MSSQL full-text indexes for searching in a database from a webpage. Is there an UDF out there that would 'bake' in data for that, or is there a simple way to solve this with regexps... ? If not, I would have to look into that myself (if not regexps, the string needs to be looped). If the form contains:   apple pear I would like the UDF to convert it to:   apple NEAR pear If the form contains:   apple AND pear (or instead of AND, NEAR or OR) I would like the UDF to keep that. If the form contains:   "apple pear" I would like the UDF to keep that. Here comes the tricky bit: If the form contains:   apple "pear peach" I would like the UDF to convert it to:   apple NEAR "pear peach" (i.e. it shouldn't touch anything inside quotes) Any help? /Hugo ------------------------------------------------------------- Hugo Ahlenius                  E-Mail: hugo.ahlenius@grida.no Project Officer                Phone:            +46 8 230460 UNEP GRID-Arendal              Fax:              +46 8 230441 Stockholm Office               Mobile:         +46 733 467111                                WWW:       http://www.grida.no ------------------------------------------------------------- ########################################### This message has been scanned by F-Secure Anti-Virus for Microsoft Exchange. For more information, connect to http://www.F-Secure.com/

Have you ruled out Verity? Hi, I am using the MSSQL full-text indexes for searching in a database from a webpage. Is there an UDF out there that would 'bake' in data for that, or is there a simple way to solve this with regexps... ? If not, I would have to look into that myself (if not regexps, the string needs to be looped). If the form contains:   apple pear I would like the UDF to convert it to:   apple NEAR pear If the form contains:   apple AND pear (or instead of AND, NEAR or OR) I would like the UDF to keep that. If the form contains:   "apple pear" I would like the UDF to keep that. Here comes the tricky bit: If the form contains:   apple "pear peach" I would like the UDF to convert it to:   apple NEAR "pear peach" (i.e. it shouldn't touch anything inside quotes) Any help? /Hugo ------------------------------------------------------------- Hugo Ahlenius                  E-Mail: hugo.ahlenius@grida.no Project Officer                Phone:            +46 8 230460 UNEP GRID-Arendal              Fax:              +46 8 230441 Stockholm Office               Mobile:         +46 733 467111                                WWW:       http://www.grida.no ------------------------------------------------------------- ########################################### This message has been scanned by F-Secure Anti-Virus for Microsoft Exchange. For more information, connect to http://www.F-Secure.com/

Yes, cause verity (at least in CF 4, which we are partly on) can't search things in the database. And the MSSQL full-text indexing seems very good to me! ------------------------------------------------------------- Hugo Ahlenius                  E-Mail: hugo.ahlenius@grida.no Project Officer                Phone:            +46 8 230460 UNEP GRID-Arendal              Fax:              +46 8 230441 Stockholm Office               Mobile:         +46 733 467111                                WWW:       http://www.grida.no ------------------------------------------------------------- | -----Original Message----- | From: Robertson-Ravo, Neil (RX) | [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] | Sent: Monday, June 30, 2003 11:13 | To: CF-Talk | Subject: RE: UDF for MSSQL full-text searches | | | Have you ruled out Verity? | | -----Original Message----- | | Sent: 30 June 2003 10:17 | To: CF-Talk | Subject: UDF for MSSQL full-text searches | | | Hi, | | I am using the MSSQL full-text indexes for searching in a | database from a | webpage. Is there an UDF out there that would 'bake' in data | for that, or | is there a simple way to solve this with regexps... ? If not, | I would have | to look into that myself (if not regexps, the string needs to | be looped). | | If the form contains: |   apple pear | I would like the UDF to convert it to: |   apple NEAR pear | | If the form contains: |   apple AND pear (or instead of AND, NEAR or OR) | I would like the UDF to keep that. | | If the form contains: |   "apple pear" | I would like the UDF to keep that. | | Here comes the tricky bit: | If the form contains: |   apple "pear peach" | I would like the UDF to convert it to: |   apple NEAR "pear peach" | | (i.e. it shouldn't touch anything inside quotes) | | Any help? | | /Hugo | | | | ------------------------------------------------------------- | Hugo Ahlenius                  E-Mail: hugo.ahlenius@grida.no | Project Officer                Phone:            +46 8 230460 | UNEP GRID-Arendal              Fax:              +46 8 230441 | Stockholm Office               Mobile:         +46 733 467111 |                                WWW:       http://www.grida.no | ------------------------------------------------------------- | | | | | ########################################### | | This message has been scanned by F-Secure Anti-Virus for Microsoft | Exchange. | For more information, connect to http://www.F-Secure.com/ | | |

not sure about 4, but verity can certainly search within a Database.... I must admit, I have never had to use the full text searches as our server guys dont like the load they cause. Yes, cause verity (at least in CF 4, which we are partly on) can't search things in the database. And the MSSQL full-text indexing seems very good to me! ------------------------------------------------------------- Hugo Ahlenius                  E-Mail: hugo.ahlenius@grida.no Project Officer                Phone:            +46 8 230460 UNEP GRID-Arendal              Fax:              +46 8 230441 Stockholm Office               Mobile:         +46 733 467111                                WWW:       http://www.grida.no ------------------------------------------------------------- | -----Original Message----- | From: Robertson-Ravo, Neil (RX) | [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] | Sent: Monday, June 30, 2003 11:13 | To: CF-Talk | Subject: RE: UDF for MSSQL full-text searches | | | Have you ruled out Verity? | | -----Original Message----- | | Sent: 30 June 2003 10:17 | To: CF-Talk | Subject: UDF for MSSQL full-text searches | | | Hi, | | I am using the MSSQL full-text indexes for searching in a | database from a | webpage. Is there an UDF out there that would 'bake' in data | for that, or | is there a simple way to solve this with regexps... ? If not, | I would have | to look into that myself (if not regexps, the string needs to | be looped). | | If the form contains: |   apple pear | I would like the UDF to convert it to: |   apple NEAR pear | | If the form contains: |   apple AND pear (or instead of AND, NEAR or OR) | I would like the UDF to keep that. | | If the form contains: |   "apple pear" | I would like the UDF to keep that. | | Here comes the tricky bit: | If the form contains: |   apple "pear peach" | I would like the UDF to convert it to: |   apple NEAR "pear peach" | | (i.e. it shouldn't touch anything inside quotes) | | Any help? | | /Hugo | | | | ------------------------------------------------------------- | Hugo Ahlenius                  E-Mail: hugo.ahlenius@grida.no | Project Officer                Phone:            +46 8 230460 | UNEP GRID-Arendal              Fax:              +46 8 230441 | Stockholm Office               Mobile:         +46 733 467111 |                                WWW:       http://www.grida.no | ------------------------------------------------------------- | | | | | ########################################### | | This message has been scanned by F-Secure Anti-Virus for Microsoft | Exchange. | For more information, connect to http://www.F-Secure.com/ | | |

I don't think that MSSQL full text causes any more of a load than Verity does. - Calvin ----- Excess quoted text cut - see Original Post for more -----

> I don't think that MSSQL full text causes any more of a load than Verity > does. well it causes it on another server at least. plenty of reasons to use full-text in place of verity: - full text searches benefit from sql server's caching, so the more you search the faster they get, verity searches are flat flat flat. - verity never worked properly with unicode, sql server does (at least since 7). - you can combine full-text & "normal" WHERE clauses in the same sql - you do JOINs w/full-text - full-text wins awards (in academic circles) for its searching technology

It is not the search that is the major load, it is the indexing. Using MSSQL, one can set the indexing to be performed in the background, and also triggered by changes in the indexed table. AFAIK, Verity needs to do full indexes each time, which can take quite some time. (NB. I don't have any experience with Verity above CF4) ------------------------------------------------------------- Hugo Ahlenius                  E-Mail: hugo.ahlenius@grida.no Project Officer                Phone:            +46 8 230460 UNEP GRID-Arendal              Fax:              +46 8 230441 Stockholm Office               Mobile:         +46 733 467111                                WWW:       http://www.grida.no ------------------------------------------------------------- | -----Original Message----- | | Sent: Monday, June 30, 2003 12:48 | To: CF-Talk | Subject: Re: UDF for MSSQL full-text searches | | | I don't think that MSSQL full text causes any more of a load | than Verity | does. | | - Calvin | | ----- Original Message ----- | From: "Robertson-Ravo, Neil (RX)" | <Neil.Robertson-Ravo@csd.reedexpo.com> | To: "CF-Talk" <cf-talk@houseoffusion.com> | Sent: Monday, June 30, 2003 5:37 AM | Subject: RE: UDF for MSSQL full-text searches | | | > not sure about 4, but verity can certainly search within a | Database.... | > | > I must admit, I have never had to use the full text | searches as our server | > guys dont like the load they cause. | > | > -----Original Message----- | | > Sent: 30 June 2003 10:41 | > To: CF-Talk | > Subject: RE: UDF for MSSQL full-text searches | > | > | > Yes, cause verity (at least in CF 4, which we are partly | on) can't search | > things in the database. And the MSSQL full-text indexing | seems very good | > to me! | > | > | > | > | > ------------------------------------------------------------- | > Hugo Ahlenius                  E-Mail: hugo.ahlenius@grida.no | > Project Officer                Phone:            +46 8 230460 | > UNEP GRID-Arendal              Fax:              +46 8 230441 | > Stockholm Office               Mobile:         +46 733 467111 | >                                WWW:       http://www.grida.no | > ------------------------------------------------------------- | > | > | > | > | > | > | > | -----Original Message----- | > | From: Robertson-Ravo, Neil (RX) | > | [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] | > | Sent: Monday, June 30, 2003 11:13 | > | To: CF-Talk | > | Subject: RE: UDF for MSSQL full-text searches | > | | > | | > | Have you ruled out Verity? | > | | > | -----Original Message----- | > | | > | Sent: 30 June 2003 10:17 | > | To: CF-Talk | > | Subject: UDF for MSSQL full-text searches | > | | > | | > | Hi, | > | | > | I am using the MSSQL full-text indexes for searching in a | > | database from a | > | webpage. Is there an UDF out there that would 'bake' in data | > | for that, or | > | is there a simple way to solve this with regexps... ? If not, | > | I would have | > | to look into that myself (if not regexps, the string needs to | > | be looped). | > | | > | If the form contains: | > | apple pear | > | I would like the UDF to convert it to: | > | apple NEAR pear | > | | > | If the form contains: | > | apple AND pear (or instead of AND, NEAR or OR) | > | I would like the UDF to keep that. | > | | > | If the form contains: | > | "apple pear" | > | I would like the UDF to keep that. | > | | > | Here comes the tricky bit: | > | If the form contains: | > | apple "pear peach" | > | I would like the UDF to convert it to: | > | apple NEAR "pear peach" | > | | > | (i.e. it shouldn't touch anything inside quotes) | > | | > | Any help? | > | | > | /Hugo | > | | > | | > | | > | ------------------------------------------------------------- | > | Hugo Ahlenius                  E-Mail: hugo.ahlenius@grida.no | > | Project Officer                Phone:            +46 8 230460 | > | UNEP GRID-Arendal              Fax:              +46 8 230441 | > | Stockholm Office               Mobile:         +46 733 467111 | > |                                WWW:       http://www.grida.no | > | ------------------------------------------------------------- | > | | > | | > | | > | | > | ########################################### | > | | > | This message has been scanned by F-Secure Anti-Virus for Microsoft | > | Exchange. | > | For more information, connect to http://www.F-Secure.com/ | > | | > | | > | | > | > |

Hugo, Note that I wasn't speaking about searching. Correct about MSSQL, in either case, both solutions require that the database be indexed with some degree of frequency, the amount of that freqency is dependent upon your need for accuracy in real time. However, Verity will be creating load on both the database server it is indexing and the coldfusion server it resides on. Whether this is better overall than the MSSQL indexing, will depend on a number of factors regarding a specific environment. - Calvin ----- Excess quoted text cut - see Original Post for more -----

> Correct about MSSQL, in either case, both solutions require that the > database be indexed with some degree of frequency, the amount of that > freqency is dependent upon your need for accuracy in real time. full-text indexing engine can "know" whats been changed if your indexed rows are TIMESTAMPed, the actual data thats re-indexed is usually therefore much smaller than the whole. you can do the same in verity but its more load on servers as well as needing an extra mechanism to be coded. full-text can be setup as a "fire & forget" mode.

probably right, but you know what DBA's are like.......anything which is out of the hands of the organiser it deemed "too much load" I don't think that MSSQL full text causes any more of a load than Verity does. - Calvin ----- Excess quoted text cut - see Original Post for more -----

> Yes, cause verity (at least in CF 4, which we are partly on) > can't search things in the database. Uh, yes it can. I've been using Verity to build indexes of database content long before CF 4. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444