Select patents according to a IPC class symbol

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

javier.lopez.otero
Posts: 2
Joined: Tue Jul 19, 2022 11:04 am

Select patents according to a IPC class symbol

Post by javier.lopez.otero » Tue Jul 19, 2022 11:52 am

Dear EPO support team,

I would like to know which should be the SQL code to obtain the patents which belong to two or more specific IPC class symbols.

Than you very much


Patent Information Marketing
Posts: 358
Joined: Fri Mar 03, 2017 1:16 pm

Re: Select patents according to a IPC class symbol

Post by Patent Information Marketing » Wed Jul 20, 2022 7:00 am

Dear User,
you may find the following query helpful.

Comment:
If you want to get all appln_ids remove the top 100,which will generate a very huge list.

Select top 100 count(distinct ipc_class_symbol) number_of_IPCs, appln_id from tls209_appln_ipc
group by appln_id
having count(distinct ipc_class_symbol)>1
Kind regards

Patent Information Marketing


EPO / PATSTAT Support
Posts: 425
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Select patents according to a IPC class symbol

Post by EPO / PATSTAT Support » Thu Aug 04, 2022 1:15 pm

An easy solution to find patents that have 2 specific classification codes is the following query.
Assume you are looking for patents that combine paint (coatings) with solar cells.
Assuming we take the following CPC codes (just as an example, in reality you would also consider IPC codes):
C09D5: https://worldwide.espacenet.com/patent/ ... C=C09D5/00
H01L31/042: https://worldwide.espacenet.com/patent/ ... H01L31/042

Retrieving all the patents in PATSTAT that have those 2 CPC codes can be done with this SQL query:

Code: Select all

SELECT a.*
FROM tls201_appln a
WHERE 
a.appln_id in 
(select appln_id  from tls224_appln_cpc where cpc_class_symbol like 'C09D   5%')
and 
a.appln_id in 
(select appln_id  from tls224_appln_cpc where cpc_class_symbol = 'H01L  31/042')
Or an example with IPC:

Code: Select all

SELECT a.*
FROM tls201_appln a
WHERE 
a.appln_id in 
(select appln_id  from tls209_appln_ipc where ipc_class_symbol = 'C09D   5/24')
and 
a.appln_id in 
(select appln_id  from tls209_appln_ipc where ipc_class_symbol = 'H01L  31/042')
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


javier.lopez.otero
Posts: 2
Joined: Tue Jul 19, 2022 11:04 am

Re: Select patents according to a IPC class symbol

Post by javier.lopez.otero » Thu Aug 18, 2022 7:00 pm

Thank you very much for your response.


jesse99
Posts: 1
Joined: Wed Aug 09, 2023 2:44 am

Re: Select patents according to a IPC class symbol

Post by jesse99 » Wed Aug 09, 2023 2:53 am

To retrieve patents that belong to two or more specific IPC class symbols using SQL, you can use a query like this:

Code: Select all

SELECT DISTINCT PatentNumber
FROM PatentIPC
WHERE IPCClassSymbol IN ('IPC_Class1', 'IPC_Class2')
GROUP BY PatentNumber
HAVING COUNT(DISTINCT IPCClassSymbol) >= 2;
Replace `'IPC_Class1'` and `'IPC_Class2'` with the specific IPC class symbols you want to query for.


Post Reply