Applicant Name with apostrophe - error

[Gauthier B.]

Dear community,

I have encountered a problem with the following query, insofar the name of the applicant as it is written (with an apostrophe 'CEA (COMMISSARIAT A L'ENERGIE ATOMIQUE)') results in error. Such name has been retrieved when checking the public entities with the largest nr of patents. There must be a way to solve this problem, but I have not found any useful information in the manual... I hope you can help me with this:

SELECT distinct psn_name, person_ctry_code, appln_auth, appln_nr, appln_kind, appln_filing_date, nb_citing_docdb_fam , publn_date as date_of_grant
FROM
tls201_appln a
join tls207_pers_appln b on a.appln_id = b.appln_id
join tls206_person c on b.person_id = c.person_id
join tls801_country on c.person_ctry_code= tls801_country.ctry_code
join tls211_pat_publn on a.appln_id = tls211_pat_publn.appln_id
WHERE
appln_auth IN ('EP','US')
and appln_filing_year between 2000 and 2020
and granted = 'Y'
and applt_seq_nr > 0 and invt_seq_nr = 0
and publn_first_grant = 'Y'
AND appln_kind = 'A'
AND docdb_family_size>2
and psn_name = 'CEA (COMMISSARIAT A L'ENERGIE ATOMIQUE)'
ORDER BY psn_name, appln_filing_date

Big thanks!

[EPO / PATSTAT Support]

Hello Gauthier,
this is not really a PATSTAT issue so you will not find anything on the use of apostrophes in the PATSTAT data catalog. Your issue is about how SQL (and the database setup) deal with quotes and apostrophes.
When the text itself contains an apostrophe, then it conflicts with the apostrophes that are used to define a string of text. Simply duplicate the apostrophe within the text and it will work fine.

Code: Select all

SELECT distinct psn_name, person_ctry_code, appln_auth, appln_nr, appln_kind, appln_filing_date, nb_citing_docdb_fam , publn_date as date_of_grant
FROM
tls201_appln a
join tls207_pers_appln b on a.appln_id = b.appln_id
join tls206_person c on b.person_id = c.person_id
join tls801_country on c.person_ctry_code= tls801_country.ctry_code
join tls211_pat_publn on a.appln_id = tls211_pat_publn.appln_id
WHERE
appln_auth IN ('EP','US')
and appln_filing_year between 2000 and 2020
and granted = 'Y'
and applt_seq_nr > 0 and invt_seq_nr = 0
and publn_first_grant = 'Y'
AND appln_kind = 'A'
AND docdb_family_size>2
and psn_name = 'CEA (COMMISSARIAT A L''ENERGIE ATOMIQUE)'
ORDER BY psn_name, appln_filing_date

I find website very useful to lookup SQL syntax when I am not sure. Keep in mind that on PATSTAT Online, you will be blocked from doing anything that changes the source data. (update, delete, insert, making tables, etc... are not possible). There can also be small differences depending on the flavour of SQL you are using. PATSTAT Online is based on a MS SQL platform, but there are still a couple of differences because of the parser.
https://www.w3schools.com/sql/