i2b2 Developer's Forum
Space shortcuts
Space Tools

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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="b3433ddf2699d2e6-29c771e2-40af4e80-aace9682-68022db5c98a003875c5b3e4"><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="12781528ccabd52f-97a1ef41-46b84346-937abe8e-73eb0b4d74de5149fd8e30c0"><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="690121f7a74295c8-c34cc7b4-44a141a3-86b3b798-bc222f2aede91260d59c895a"><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="b47f5c7c2ed0848a-1bd127af-48a542bd-925dbc22-502174a156bf6207340b09ea"><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="bc7121d4c2577dab-325fda7c-494b43b0-a30597fb-eecdd4cc987893d90bda5081"><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="d0e00a911ac3183c-b916e791-4342482d-b1a3be86-de7ae8f346323adb8bfeab14"><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="c3ff1db44fae49e6-2d39df8c-4cb94b80-bae08537-3469cff068cf3b5cedeb3f63"><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="1baeaa6764ee68b4-185b4fe8-425646a0-a1df88b9-e2b37619844659a8c3992781"><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%'

...