Extract all patents except the ones that have specific words

[JohnVoyore]

HI, how can I access all the patents except the one that I specify in the title or abstract?
Example: I want all patents filed in WO (PCT) except those that have artificial intelligence in the title between 2015-2020. How can I find it?

[EPO / PATSTAT Support]

Hello Johnvoyore,

There are several ways of doing that. On PATSTAT Online, the easiest and quickest method is to use the full text search functionality.

Something like this:

Code: Select all

SELECT *
FROM tls201_appln 
JOIN  tls202_appln_title ON tls201_appln.appln_id = tls202_appln_title.appln_id
WHERE
NOT CONTAINS (appln_title, '"artificial intelligence" ')  
AND appln_filing_year between 2015 and 2020
AND appln_auth = 'WO'

This query will create nearly 1.4 million records.
Another method would me to use the "LIKE" operator.
Something like this:

Code: Select all

WHERE appln_title not like '%artificial intelligence%'

[JohnVoyore]

Thank you for your answer. That's a lot. How to include the abstract?
is it correct like this or I have to do inner join?

Code: Select all

SELECT *
FROM tls201_appln 
JOIN  tls202_appln_title ON tls201_appln.appln_id = tls202_appln_title.appln_id
JOIN   TLS203_APPLN_ABSTR  ON (tls201_appln.appln_id = tls203_appln_abstr.appln_id) 
WHERE(appln_title not like '%artificial intelligence%' or appln_abstract  not like '%artificial intelligence%')
AND appln_filing_year between 2015 and 2020
AND appln_auth = 'WO'