PATSTAT 2017 Autumn - text search operators and non full-text Index table error

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

aaryan.thakur
Posts: 1
Joined: Wed Feb 14, 2018 3:36 pm

PATSTAT 2017 Autumn - text search operators and non full-text Index table error

Post by aaryan.thakur » Wed Feb 14, 2018 5:38 pm

I am working with PATSTAT 2017 autumn. I have couple of queries mentioned below:
(1) I am new to PATSTAT database. I need to run a few queries which requires search in application title and application abstract fields. The only text search operator I know is "%". Can anybody please tell me or provides me with any link to show what other text search operators PATSTAT supports.
(2) I tried using CONTAINS (with NEAR) operator in application title column but it is showing error "Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'tls202_appln_title' because it is not full-text indexed". Can anybody please tell me how to convert this table to full-text indexed table or how to use NEAR operator between two or more keywords.

Thanks a lot in advance!


mkracker
Posts: 95
Joined: Wed Sep 04, 2013 6:17 am
Location: Vienna

Re: PATSTAT 2017 Autumn - text search operators and non full-text Index table error

Post by mkracker » Thu Feb 15, 2018 10:44 am

I suppose you are using PATSTAT Online. PATSTAT Online is powered by MS SQL Server, which is using the T-SQL dialect of SQL. There are some more wildcards than just the %-character. Please see the full Microsoft documentation here: https://docs.microsoft.com/en-us/sql/t- ... ansact-sql
When working with text in PATSTAT, please consider that not all abstracts / titles are in English, but in a variety of languages.

The strength of PATSTAT Online is the fast analysis of structured patent information. Text data is limited to titles of inventions and abstracts. There is no full text index, so CONTAINS, NEAR etc. will not work.

IMO your options are:
  • Use wild cards to roughly define the result set you are interested in. Download it and apply a text processing tool of your choice to further limit and analyse it,
  • Get PATSTAT raw data to create your own database and adapt it any way you need, or
  • Use other tools which are better suited for text search, like EPO's Global Patent Index, Espacenet or EP full-text search.
I hope this helps,
Martin
-------------------------------------------
Martin Kracker / EPO PATSTAT


Post Reply