This is the repository of all functions and stored procedures specially developed for the manipulation of PATSTAT and/or similar databases. These are :
COMPAREIPC(ipc1, ipc2): Compares ipc1 and ipc2 at section, class, subclass, group and subgroup returning respectevely 1,2,3,4 and 5 (or 6) values. 5 is returned when there is match at 2 digits subgroup level, but not at higher one, 6 is retuned when subgroup is matched at full extent. Notice that if IPC1 and IPC2 match at 3 but not at 4 subgroup digits then a two digit level match result (meaning 5) is returned. This is due to the fact that hyerchy is not straight forward in IPC coding. In current version subgroup 00 is considered as any other 2 digit subgroup. Zero is returned if there no match at all.
DECORRUPT(string): Cleans PATSTAT (and only PATSTAT) from corrupted characters.
DEDOUBLE(string): Cleans up all double, heading and trailing spacing from any given string.
DWABBREV(string): Replaces parts of a clean string based on the Derwent Patentee Codes Abbreviation list (annex 3).
DWABBREVTAG(string): Returns each abbreviated element (space delimited) from of a clean string based on the Derwent Patentee Codes Abbreviation (annex 3).
DWLVABBREV(string): Replaces parts of a clean string based on a mix of the Derwent Patentee Codes Abbreviation list (annex 3) and the K.U.Leuven FETEW MSI Research report 0605 (appendix 2).
JUSTABC(string): Removes everything from any given string thats is not one of the 26 Latin alphabet (A to Z) or a blank space.
JUSTABC123(string): Removes everything from any given string thats is not one of the 26 Latin alphabet (A to Z), a number (0-9) or a blank space.
LVLEGALFORM(string): Replaces parts of any given string based on the K.U.Leuven FETEW MSI Research report 0605 (appendix 2). This function is the closest to the appendix of the mentioned report and can be applied to rough strings as well. However, LVLEGALFORMC is much performant in computational time.
LVLEGALFORMC(string): Replaces parts of a clean string based on the K.U.Leuven FETEW MSI Research report 0605 (appendix 2). This function is very similar to LVLEGALFORM, but is much performant in computational time.
NOACCENTS(string): Removes any accentuated or special character (e.g. as á, ñ or ø) from any given text replacing them by the non accentuated version of them (e.g. a, n or o). Characters such as ä or ü are replaced by their direct non accentuated versions a or u. See the full criteria here.
COMPARE_ARRAY(array1, array2): Returns 1 if array1 shares at least one element with array2. As an array there is considered any given string with 1 or more elements separated by commas.
This wiki