1.6.02
The i2b2 metadata allows a text search of the observation_fact table. Two columns may be searched, the TVal_Char column, and the observation_blob column (coming soon). The search is initiated by pulling concepts from the "Navigate Terms" or "Find Terms" in the Vocabulary Areas of the i2b2 Client for which you want to search for values in their associated text. There are several strategies undertaken while loading the data where values may have been included that one wants to search, such as:
...
When the term is dragged, the value selection box will be presented to the user, the STRING tag will cause the following box to be presented:
Image Modified
Note that the "Family History Problem" is a concept code that has a value, which is text in the TVal_Char column, and the value will be searched for the word/phrase "mother". The STRING query box allows the following options for how the string value will be searched:
...
Text constrain options | Sql |
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="677f07b1-3f0b-4679-9943-d1c7babd866d"><ac:plain-text-body><![CDATA[ | LIKE[exact] ]]></ac:plain-text-body></ac:structured-macro> Case insensitive search.
<constrain_by_value> <value_type>TEXT</value_type> <ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="ecd35fcb-2cc9-4403-8838-94bea868dde6"><ac:plain-text-body><![CDATA[ <value_operator> LIKE[exact]</value_operator> ]]></ac:plain-text-body></ac:structured-macro> <value_constraint> NEG </value_constraint> </constrain_by_value> | Select * from observation_fact where Concept_cd in (select concept_Cd from concept_dimension where ..) and valtype_cd = 'T' and upper(tval_char) = upper('NEG') | <ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="1f3d25e9-6f3f-446c-b8c4-bdaf8a5ed0a4"><ac:plain-text-body><![CDATA[ |
LIKE[begin] ]]></ac:plain-text-body></ac:structured-macro> Begin will be the default option if the optional tag is not present. This will support backward compatibility.
<constrain_by_value> <value_type>TEXT</value_type> <ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="ff19a6ae-c795-4a2b-8420-81ae5b0edcc3"><ac:plain-text-body><![CDATA[ <value_operator>LIKE[begin]</value_operator> ]]></ac:plain-text-body></ac:structured-macro> <value_constraint> NEG </value_constraint> </constrain_by_value>
| Select * from observation_fact where Concept_cd in (select concept_Cd from concept_dimension where ..) and valtype_cd = 'T' and upper(tval_char) LIKE upper('NEG%') | <ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="70e54ba5-9e66-40bf-b177-a7554c8acc5b"><ac:plain-text-body><![CDATA[ |
LIKE[end] ]]></ac:plain-text-body></ac:structured-macro>
<constrain_by_value> <value_type>TEXT</value_type> <ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="8581f22b-2939-4be0-ac0c-3e1d194d107d"><ac:plain-text-body><![CDATA[ <value_operator>LIKE[end]</value_operator> ]]></ac:plain-text-body></ac:structured-macro> <value_constraint> NEG </value_constraint> </constrain_by_value>
| Select * from observation_fact where Concept_cd in (select concept_Cd from concept_dimension where ..) and valtype_cd = 'T' and tval_char LIKE '%NEG' | <ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="faea12ab-beb1-423c-bc87-b6c9b7efc927"><ac:plain-text-body><![CDATA[ |
LIKE[contains] ]]></ac:plain-text-body></ac:structured-macro>
<constrain_by_value> <value_type>TEXT</value_type> <ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="d67ef85b-2f31-4075-8c43-6076a9784d9f"><ac:plain-text-body><![CDATA[ <value_operator>LIKE[contains] </value_operator> ]]></ac:plain-text-body></ac:structured-macro> <value_constraint>NEG </value_constraint> </constrain_by_value>
| Select * from observation_fact where concept_cd in (select concept_Cd from concept_dimension where ..) and valtype_cd = 'T' and tval_char LIKE '%NEG%' |
...