Need help with a query ....

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Griff - 08 Dec 2005 12:37 GMT

I have a database table 'epda' that has a full text catalogue on it.

The field of interest 'epd' contains two rows of interest.

The first row has the following example text:
'This is some text and x73 is the value'

The second row of interest has the following example text:
'This is some text and
Z42/Z43/Z45/Z51/Z52/Z53/Z54/X63/X70/X73/X80/X83/X84/X85/X125/A10 are the
values"

I create a full text catalogue of this and have a SQL query that contain the
following WHERE statement:
(CONTAINS(epd, ' "X73" '))

This finds the first row, but not the second row.

Replacing it with
(epd LIKE '%x73%')

Of course finds both rows.

The problem is that the SQL query is generated dynamically and needs to use
the full text catalogue search.

It is almost as if the full text catalogue has ignored the terms with a / in
it.

Any advice would be most welcome...

Thanks

Griff

Reply to this Message

mattp - 08 Dec 2005 12:56 GMT

As per BOL: "contains" predicate is based on words (phrases), that are
separated by space or "punctuation". I'd assume your slash to separate
multiple values (second row) isn't 'a "puntuation". (BOL is not clear
on that). Consider separating the VALUES by comma or additioal spaces.

Reply to this Message

Griff - 08 Dec 2005 13:41 GMT

Ah - but back in the days when I were a lad, punctuation included the "/"
(or is it just that my mind that has faded over the years?).

Either way, the text containing the "/" characters is provided by suppliers
and must be displayed "as is".

So, back to the drawing board.

Thanks anyhow Matt

> As per BOL: "contains" predicate is based on words (phrases), that are
> separated by space or "punctuation". I'd assume your slash to separate
> multiple values (second row) isn't 'a "puntuation". (BOL is not clear
> on that). Consider separating the VALUES by comma or additioal spaces.

Reply to this Message

mattp - 08 Dec 2005 16:19 GMT

Hi Griff,

you're probably right - this must have changed in the years :-)

This seems to be gettin truly complicated ...
I'll just point you to an older thread I found - seems to be a known
symptom
(some sort of OS-supplied DLL is responsibel for the "word breeaking").

http://groups.google.de/group/microsoft.public.sqlserver.fulltext/browse_thread/
thread/69f31b07c6c75834/9bd775cda7af0074?lnk=st&q=sql+server+punctuation+charact
ers&rnum=5&hl=de#9bd775cda7af0074

One idea though: couldn't you "copy" the columns provided "by the
suppiers" to a second one, do some string translation,
and use this column for full-text indexing ?
(of course do be done "regulary" - perhaps by a trigger )

Running out of ideas else ...

Reply to this Message

Griff - 09 Dec 2005 09:47 GMT

The second column idea - like your thinking on this one....may have to go
with that.

Thanks!

Reply to this Message

mattp - 09 Dec 2005 11:07 GMT

You're welcome - hope that works out. There seems to be scalar function
"REPLACE" that should come in handy for this ...

Reply to this Message

Hilary Cotter - 09 Dec 2005 18:56 GMT

Hate to say this to you but this works on my machine - SQL 2000 sp4,
Win2003.

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

>I have a database table 'epda' that has a full text catalogue on it.
>
[quoted text clipped - 30 lines]
>
> Griff

Reply to this Message