Full text search - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Full text search

Author

Topic

fdonders
Starting Member

6 Posts

Posted - 2007-09-19 : 05:19:21

I have setup full text search on a table. Everything works fine. Now I want to find documents that contain the next string: 'T-150/04'

The query will give zero results. Is there a way to find documents that contain these kind of strings? (slahses and minus)

Regards,

Freek

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-19 : 05:20:48

What is the query you using?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

fdonders
Starting Member

6 Posts

Posted - 2007-09-19 : 09:57:39

I use the following query:

SELECT * FROM CONTAINSTABLE (jurisprudentie, text, '"T-150/04"' )

If I do this query:

SELECT * FROM CONTAINSTABLE (jurisprudentie, text, '"judge"' )

It will work fine. I know it has to do something with the interpunction things (slahs and minus), but I don't know what query may solve my problem.

rcurrey
Starting Member

30 Posts

Posted - 2007-09-19 : 16:44:55

Try this -

Select * from containstable(jurisprudentie, text, '"T_150_04"' )

This should return all rows where there is any character after the T and any character after the 150. I am assuming that this is a specific pattern that you are searching for.

Thanks,
Rich

fdonders
Starting Member

6 Posts

Posted - 2007-09-20 : 09:51:37

Unfortunatly, It still does not give me any results. If I search for a 'normal' word in the same record, I will find that record.

The minus sign and the slash are disturbing things. If i search for "150" or for "04" I will find the record, but I would like to speficically search for the exact string "T-150/04". Because these are references to case law issued by the court.

I hope someone can help me out.

Regards,

Freek

rcurrey
Starting Member

30 Posts

Posted - 2007-09-21 : 16:37:31

Sorry - Let's try it again ....

Select * from containstable(jurisprudentie, text, '"T[-]150[/]04"' )

The [] means this specific character.

Thanks,
Rich

fdonders
Starting Member

6 Posts

Posted - 2007-09-23 : 05:20:29

It doesn't seem to work.

if I enter: Select * from containstable(jurisprudentie, text,
'"150[/]"' )

I will find the record (among a lot of other records)

if I enter: Select * from containstable(jurisprudentie, text,
'"150[/]04"' )

I find 0 records.

Kristen
Test

22859 Posts

Posted - 2007-09-23 : 06:32:07

Try:

SELECT * FROM CONTAINSTABLE (jurisprudentie, text, '"T 150 04"' )

i.e. punctuation replaced with space. This should retrieve matching records with those adjacent "words", but that won't be an exact match for your original "T-150/04"necessarily.

You could add additional logic to refine the matches:


SELECT *
FROM  jurisprudentie AS J
      JOIN CONTAINSTABLE (jurisprudentie, text, '"T 150 04"' ) AS K
          ON K.[Key] = J.MyPK
WHERE J.[text] LIKE '%T-150/04%'

Kristen

fdonders
Starting Member

6 Posts

Posted - 2007-09-24 : 05:26:45

I can not use the LIKE, because the datatype is XML.

Is there really no simple way to search for a literal string?

Regards

Freek

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 05:48:28

"Is there really no simple way to search for a literal string?"

Its a free text search tool, it isn't designed to find a literal string, only semantically similar words. In addition to punctuation it won't find "noise words" either.

For an exact match you need to use equals or like comparisons rather than the free text searching method.

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-24 : 23:35:04

Can you build full text index on xml type column?

fdonders
Starting Member

6 Posts

Posted - 2007-09-25 : 04:06:32

Sure. You can also build full text index on datatypes that contain PDF-files or word documents. In most of the cases these fields contain a lot of searchable content. Therefor a fulltext index is so useful, but you can not use equal/like statements anymore.

Still I would like to find the records I want and in my opion it is strange if there is no way to find a document with a dosiernumber that contains punctuation or slahses.

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 04:45:37

"in my opion it is strange "

I know where you are coming from

but I think you need to look at it differently.

The Free Text tool is trying to find words that are similar - plurals, synonyms, different tenses of verbs, etc.

In fact quite the opposite of an exact match! being essentially a fuzzy match

To do this it makes a list of all the roots of all the words, and excludes all the noise words (a, the, and, etc.). Its pretty hard to get from there to any sort of exact match.

Google does have some sort of way of achieving that, but it certainly isn't perfect. Searching for a phrase enclosed in quotes, in Google, will probably find what you are looking for - punctuation and all. But it won't [for example] work with the highlighting in the Google toolbar, which is still stuck with splitting into words and stuff like that.

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-25 : 23:07:14

From books online:

You can build full-text indexes on columns that contain char, varchar and nvarchar data. Full-text indexes can also be built on columns that contain formatted binary data, such as Microsoft Word documents, stored in a varbinary(max) or image column.

Didn't mention xml data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources