UMLS Database Query Diagrams:
How to perform searches for normalized strings, normalized words, or regular words

This diagram shows how to perform searches for normalized strings (NSTR), normalized words (NWD), or regular words (WD). Normalized string and normalized word searches expect that strings or words have been processed by the NORM program, one of the Lexical Tools. For more information on the Lexical Tools in the UMLS, look here: //lexsrv3.nlm.nih.gov/Specialist/Home/index.html

When performing normalized string searches in MRXNS_ENG.RRF, the results will yield a CUI, LUI, SUI tuple. To find matching atoms, search for the CUI, LUI combination in MRCONSO.RRF. The MRCONSO.RRF LUI values are computed based on normalized string values, so it is not necessary to search for each SUI individually; a broader LUI search will suffice.

Note that regular (non-normalized) word searches involve a MRCONSO.RRF search on CUI, SUI rather than CUI, LUI.

When performing normalized word searches in MRXNW_ENG.RRF, the results will yield a CUI, LUI, SUI tuple. To find matching atoms, search for the CUI, SUI combination in MRCONSO.RRF.

When performing regular word searches in the corresponding MRXW_ENG.RRF table, the results will yield a CUI, LUI, SUI tuple. To find matching atoms, search for the CUI, SUI combination in MRCONSO.RRF.

Normalized word searches can only be performed in the English language as the NORM program is specifically written to work with English language strings.

To perform searches involving multiple words, see the queries below.

Corresponding Oracle Queries:

1. Find atoms matching a normalized string.

SELECT b.* FROM mrxns_eng a, mrconso b
WHERE a.nstr = 'abnormal bleed'
     AND a.cui = b.cui
     AND a.lui = b.lui;

2.a. Find atoms matching a word.

SELECT b.* FROM mrxw_eng a, mrconso b
WHERE a.wd = 'bleeding'
     AND a.cui = b.cui
     AND a.sui = b.sui;

2.b. Find atoms matching 2 words (Boolean AND).

SELECT c.* FROM mrxw_eng a, mrxw_eng b, mrconso c
WHERE a.wd = 'abnormal'
     AND b.wd = 'bleeding'
     AND a.cui = c.cui
     AND a.sui = c.sui
     AND b.cui = c.cui
     AND b.sui = c.sui;

2.c. Find atoms matching 2 words (Boolean OR)

SELECT b.* FROM mrxw_eng a, mrconso b
WHERE a.wd = 'abnormal'
     AND a.cui = b.cui
     AND a.sui = b.sui
UNION
SELECT b.* FROM mrxw_eng a, mrconso b
WHERE a.wd = 'bleeding'
     AND a.cui = b.cui
     AND a.sui = b.sui;

3.a. Find atoms matching a normalized word.

SELECT b.* FROM mrxnw_eng a, mrconso b
WHERE a.nwd = 'bleed'
     AND a.cui = b.cui
     AND a.sui = b.sui;

3.b. Find atoms matching 2 normalized words (Boolean AND).

SELECT c.* FROM mrxnw_eng a, mrxnw_eng b, mrconso c
WHERE a.nwd = 'abnormal'
     AND b.nwd = 'bleed'
     AND a.cui = c.cui
     AND a.sui = c.sui
     AND b.cui = c.cui
     AND b.sui = c.sui;

3.c. Find atoms matching 2 normalized words (Boolean OR).

SELECT b.* FROM mrxnw_eng a, mrconso b WHERE a.nwd = 'abnormal'
     AND a.cui = b.cui
     AND a.sui = b.sui
UNION
SELECT b.* FROM mrxnw_eng a, mrconso b
WHERE a.nwd = 'bleed'
     AND a.cui = b.cui
     AND a.sui = b.sui;

Last Reviewed: August 14, 2014