Drug Queries‎ > ‎

D02: Find drug or class by keyword

This query enables search of vocabulary entities in the drug domain by keyword. The query does a search of standard concepts names in the DRUG domain including the following:
  • RxNorm standard drug concepts
  • ETC, ATC therapeutic classes
  • NDF-RT mechanism of action, physiological effect, chemical structure concepts
  • Synonyms of drug concepts
  • Mapped drug codes from NDC, GPI, Multum, Multilex
It does not require prior knowledge of where in the vocabularies the entity is situated.

Input:
 Parameter  Example  Mandatory  Notes
 Keyword  'Lipitor'  Yes Keyword should be placed in single quote
 As of date  Sysdate  No Valid record as of specific date. Current date – sysdate is a default

Sample query run:
The following is a sample run of the query to run a search of the Drug domain (drugs and drug classes) for keyword ‘Lipitor’. The input parameters are highlighted in blue.
 SELECT C.concept_id       Entity_Concept_Id,
        C.concept_name     Entity_Name,
        C.concept_code     Entity_Code,
       'Concept'           Entity_Type,
        C.concept_class    Entity_concept_class,
        C.vocabulary_id    Entity_vocabulary_id,
        V.vocabulary_name  Entity_vocabulary_name
 FROM    vocabulary.concept     C,
         vocabulary.vocabulary  V
 WHERE   C.vocabulary_id IN (7,8,19,20,21,32,53)
 AND C.concept_class IS NOT NULL
 AND C.concept_level <> 0
 AND C.vocabulary_id = V.vocabulary_id
 AND INSTR(LOWER(REPLACE(REPLACE(C.concept_name, ' ', ''), '-', '')),
            LOWER(REPLACE(REPLACE('Lipitor', ' ', ''), '-', ''))) > 0
 AND sysdate BETWEEN C.valid_start_date AND C.valid_end_date
 UNION ALL
 SELECT NULL                       Entity_Concept_Id,
        SC.source_code_description Entity_Name,
        SC.source_code             Entity_Code,
        'Mapped Code'              Entity_Type,
        NULL                       Entity_concept_class,
        SC.source_vocabulary_id    Entity_vocabulary_id,
        V.vocabulary_name          Entity_vocabulary_name
 FROM    vocabulary.source_to_concept_map   SC,
         vocabulary.vocabulary              V
 WHERE   SC.source_vocabulary_id IN (9,10,16,22,28,46,50)
 AND SC.source_vocabulary_id = V.vocabulary_id
 AND INSTR(LOWER(REPLACE(REPLACE(SC.source_code_description, ' ', ''), '-', '')),
            LOWER(REPLACE(REPLACE('Lipitor', ' ', ''), '-', ''))) > 0
 AND sysdate BETWEEN SC.valid_start_date AND SC.valid_end_date
 UNION ALL
 SELECT C.concept_id            Entity_Concept_Id,
        S.concept_synonym_name  Entity_Name,
        C.concept_code          Entity_Code,
        'Concept Synonym'       Entity_Type,
        C.concept_class         Entity_concept_class,
        C.vocabulary_id         Entity_vocabulary_id,
        V.vocabulary_name       Entity_vocabulary_name
 FROM    vocabulary.concept           C,
         vocabulary.concept_synonym   S,
         vocabulary.vocabulary        V
 WHERE   S.concept_id = C.concept_id
 AND C.vocabulary_id IN (7,8,19,20,21,32,53)
 AND C.concept_class IS NOT NULL
 AND C.concept_level <> 0
 AND C.vocabulary_id = V.vocabulary_id
 AND INSTR(LOWER(REPLACE(REPLACE(S.concept_synonym_name, ' ', ''), '-', '')),
            LOWER(REPLACE(REPLACE('Lipitor' , ' ', ''), '-', ''))) > 0
AND sysdate BETWEEN C.valid_start_date AND C.valid_end_date
 Output:
Output field list:
 Field  Description
 Entity_Concept_ID  Concept ID of entity with string match on name or synonym concept
 Entity_Name  Concept name of entity with string match on name or synonym concept
 Entity_Code  Concept code of entity with string match on name or synonym concept
 Entity_Type  Type of entity with keyword match, includes one of the following:
  • Concept
  • Concept Synonym
  • Mapped Code
 Entity_Concept_Class  Concept class of entity with string match on name or synonym concept
 Entity_Vocabulary_ID  Vocabulary the concept with string match is derived from as vocabulary ID
 Entity_Vocabulary_Name  Name of the vocabulary the concept with string match is derived from as vocabulary code

Sample output record 1:
 Field  Value
 Entity_Concept_ID  1545999
 Entity_Name  atorvastatin 20 MG Oral Tablet [Lipitor]
 Entity_Code  617318
 Entity_Type  Concept
 Entity_Concept_Class  Branded Drug
 Entity_Vocabulary_ID  8
 Entity_Vocabulary_Name  RxNorm

Sample output record 2:
 Field  Value
 Entity_Concept_ID  
 Entity_Name  atorvastatin 20 MG Oral Tablet [Lipitor]
 Entity_Code  60491080434
 Entity_Type  Mapped Code
 Entity_Concept_Class
 Entity_Vocabulary_ID  9
 Entity_Vocabulary_Name  NDC

This is a comprehensive query to find relevant terms in the vocabulary. To constrain, additional clauses can be added to the query. However, it is recommended to do a filtering after the result set is produced to avoid syntactical mistakes.
The query only returns concepts that are part of the Standard Vocabulary, ie. they have concept level that is not 0. If all concepts are needed, including the non-standard ones, the clause in the query restricting the concept level and concept class can be commented out.
Comments