Topic outline

• Searching Text Fields with SQL Search

  Searching Text Fields with SQL Search

  Content Type
  Video

  Duration
  5m

  Level
  Introductory

  See how to use InterSystems IRIS® SQL Search indices to enhance text searches. A SQL Search index can be added to any text property in a persistent class, allowing you to improve and optimize your queries, ultimately leading to easier analytics.
• Code Used in This Video

  Code Used in This Video

  Reference	Timestamp	Code
  Select author and quote for each entry.	1:30	select Author, Quote from Learning.Quotations
  Select quotes that include a specific word.	2:10	select Author, Quote from Learning.Quotations where %ID %FIND search_index(QuoteBasicIndex, 'word1')
  Select quotes that include two specific words.	2:20	select Author, Quote from Learning.Quotations where %ID %FIND search_index(QuoteBasicIndex, 'word1 word2')
  Select quotes that include a specific phrase.	2:38	select Author, Quote from Learning.Quotations where %ID %FIND search_index(QuoteBasicIndex, ' "phrase to find" ')
  Select quotes that include two specific words separated by 2 words.	3:08	select Author, Quote from Learning.Quotations where %ID %FIND search_index(QuoteBasicIndex, 'word1 ? ? word2')
  Select quotes that include two specific words separated by up to 9 words.	3:20	select Author, Quote from Learning.Quotations where %ID %FIND search_index(QuoteBasicIndex, 'word1 [0-9] word2')
  Select quotes that do not include a specific word.	3:26	select Author, Quote from Learning.Quotations where %ID %FIND search_index(QuoteBasicIndex, 'NOT word1') ;
  Select quotes that include words starting with a specific set of letters.	3:54	select Author, Quote from Learning.Quotations where %ID %FIND search_index(QuoteBasicIndex, 'start*')
  Select quotes that include a word that is 15 characters long.	4:45	select Author, Quote from Learning.Quotations where %ID %FIND search_index(QuoteBasicIndex, '\w{14}',4)
  Select quotes that include a word that almost matches a specific word.	5:05	select Author, Quote from Learning.Quotations where %ID %FIND search_index(QuoteBasicIndex, 'word1',3)
• What's Next?

  What's Next?
  • Try searching unstructured text data with SQL Search (exercise, 15m).
  • Read more about performing a SQL Search (documentation, 20m).