I am looking for data including this field to each patent

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

Posts: 3
Joined: Thu May 01, 2014 10:11 am

I am looking for data including this field to each patent

Post by itsik » Thu May 01, 2014 10:22 am

I need your help, because I can’t find this data
I am looking for data including this field to each patent:
• application date
• patent granted
• amount of citation (for this patent till X DATE)
• Is the granted patent gave for all rows or just partly (for some rows)

The search should be by company, for example if I search "TEVA" company I should get the all patents list for the company in rows, and by columns the fields I had marked above.
I really appreciate your help, I need it for my study work
*you could see the excel down
I need to check it for each company in the S&P 500 (USA) and S&P 350 (in Europe)

Posts: 140
Joined: Wed Jul 08, 2009 5:51 pm

Re: I am looking for data including this field to each paten

Post by nico.rasters » Fri Jul 11, 2014 7:54 pm

Do you have access to a local installation of PATSTAT (if so, which version?) or PATSTAT Online?

The following query would give you a list of names that have "TEVA" in them.
SELECT * FROM `tls206_person` WHERE `person_name` REGEXP "[[:<:]]TEVA[[:>:]]";
Alternatively, you can use the tls208 table.
SELECT * FROM `tls208_doc_std_nms` WHERE `doc_std_name` LIKE "Teva Pha%";
I used a different query as I am assuming that you are looking for TEVA Pharmaceuticals.

Both queries will return a list of names. You will then have to pick out the names that are good.
For example, you will find "TEVA PHARMACEUTICALS USA CORP". No doubt a subsidiary. It's up to you to decide if you only want the headquarters in Israel or the foreign subsidiaries as well.

Once you have a list of "good" names, you can find their patents by using the tables tls206, tls207, and tls201.

Anyway, your post is already quite old so I don't know if you're still working on this. If you are, I can expand my answer.
Nico Doranov
Data Manager

Daigu Academic Services & Data Stewardship

Post Reply