SQL Practical Guide

Search

SNOMED CT Document Library

    Loading...

Page tree

  

Search Plus Procedure

The searchPlus procedure lists the conceptId and terms that match a search pattern. The search matches can also be filtered using a simple focus concept expression constraint or a regular expression pattern.

Note

This procedure is intended to demonstrate some of the search options available in MySQL and to illustrate the value of restricting searches to subtypes of concepts that in particular hierarchies or sub-hierarchies.

Additional work would be required to make a more user-friendly interface to these search facility and this is beyond the scope of the SNOMED CT example database.

Example 4.9.4-1: Example Use of searchPlus Procedure

SQL Call to Procedure

call snap_searchPlus(p_search, p_filter);

ParameterDescriptionData typeExamples

p_search

A search term string.

These searches use MySQL's fulltext index employing one of the two supported search modes depending on the string supplied.

If p_search includes plus "+" or minus "-" symbols, the search is performed using boolean mode. This means matches are only returned if all words preceded by "+" are present and none of the words "-". Word not preceded by either plus or minus, are not required to match but will contribute to the search. The results of this search are sorted by with the shortest matching terms first.

If p_search does not include "+" or "-" symbols, the MySQL natural language search is used. It also orders the returned results by 'relevance' and is intended to assist contextual searching through literature. However, our testing indicate that the boolean mode is usually more effective for SNOMED CT searches.

For more information about full text searches in MySQL please see Full-Text Search Functions in the MySQL Reference Manual.

text

'+fundus +stomach'

'+lung +disease +chronic'

'appendix'

'hemoglobin'

'infection'

p_filter

A filter that will be applied to selectively include concepts that are subtype descendants of a specified concept:

  • a simple focus concept subtype constraint.
    • Starting with a less than sign < followed either by either
      • a conceptId; or
      • a shortcut abbreviation for a commonly used concept. To see the current set of shortcuts run the following query "SELECT * FROM config_shortcutPlus;"

A regular expression pattern to be used to filter terms returned by the search string

  • a regular expression to exclude matching terms
    • Starting this with an exclamation mark ! followed by the regular expression you want to exclude for the terms found by p_search.
  • a regular expression required for inclusion
    • The text of the regular expression that must be matched for inclusion.
text

'<find'

'<proc'

'<123037004'

'!lung'

'heart'

Example Procedure Call
call snap_searchPlus('+viral +infection','< 19829001 |disorder of lung|');

Result

conceptIdterm
75570004Viral pneumonia
276692000Congenital viral pneumonia
75570004Viral pneumonia (disorder)
421508002Viral pneumonia associated with AIDS
276692000Congenital viral pneumonia (disorder)
421508002

Viral pneumonia associated with acquired immunodeficiency syndrome

421508002

Viral pneumonia associated with acquired immunodeficiency syndrome (disorder)


Feedback
  • No labels