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
Select patents according to a IPC class symbol
-
- Posts: 2
- Joined: Tue Jul 19, 2022 11:04 am
-
- Posts: 358
- Joined: Fri Mar 03, 2017 1:16 pm
Re: Select patents according to a IPC class symbol
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
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
Patent Information Marketing
-
- Posts: 440
- Joined: Thu Feb 22, 2007 5:33 pm
- Contact:
Re: Select patents according to a IPC class symbol
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:
Or an example with IPC:
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')
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
EPO - Vienna
patstat @ epo.org
-
- Posts: 2
- Joined: Tue Jul 19, 2022 11:04 am
Re: Select patents according to a IPC class symbol
Thank you very much for your response.
Re: Select patents according to a IPC class symbol
To retrieve patents that belong to two or more specific IPC class symbols using SQL, you can use a query like this:
Replace `'IPC_Class1'` and `'IPC_Class2'` with the specific IPC class symbols you want to query for.
Code: Select all
SELECT DISTINCT PatentNumber
FROM PatentIPC
WHERE IPCClassSymbol IN ('IPC_Class1', 'IPC_Class2')
GROUP BY PatentNumber
HAVING COUNT(DISTINCT IPCClassSymbol) >= 2;