1.6.0302
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:
...
Text constrain options | Sql |
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="0c251985b747d612-11d53798-4772440a-bec4a7e0-601f55dfd37c8df96f683c67"><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="b6b306920fe6c556-9b117ac6-4bb442e9-a76c8bb3-d1b79a8a0c353340e9444ff9"><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="1187feca298a91d8-4f94a6fc-410f4009-b69f999c-386cb3c9f9a9b3441d97ed86"><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="61b920929c440fbd-c9f3185d-49904e1a-ab92a177-4b79b2eec6db37ff39c94814"><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="79420537b1c9be15-f067832a-47964052-a344a4f8-0beb3252a0fa5fd39e9c49cf"><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="dda20516e6c6cb8a-9e15b19c-4b024827-a6d7825a-e836e37559c988779e47f194"><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="b8aaf89ac43ff52b-a8d03d97-4dca40b1-ad518616-8f821b15adf4f8eda13cebbb"><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="b65d146197b5731e-e739dd9a-4c5f4893-adafbd96-638872864bf01b672e739288"><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%' |
...
