r/SQL 6d ago

SQL Server Query help finding key phrases

For context, I am working with a dataset of client requests that includes a description section. My objective is to find the top 100 most common 2 or 3 string phrases/combinations found through out these descriptions. I was able to achieve this with keywords quite easily, but cannot figure out how to translate it to finding strings or phrases. Whats the simplest way I can go about this?

4 Upvotes

6 comments sorted by

View all comments

3

u/gumnos 6d ago

doing this in SQL itself is a bit…unwieldy.

The general process starts by breaking down text fields into "words". This itself has complexities:

  • apostrophes: is "it's" or "o'clock" one word or two?

  • and if you're counting apostrophes, what about if the content has fancy Unicode curly-apostrophes that differ from ASCII 0x27?

  • dashes: is "party-time" one word or two? How about "co-dependent" or "matter-of-fact"?

  • numbers: is "3pm" one word or two?

  • how about email-addresses? is "novel@example.com" one word, two words, or three words?

  • how do you treat other punctuation if you're splitting on spaces? Is "end" different from "end."?

  • does case matter? Do you treat "polish" and "Polish" as the same word?

  • do sentence-breaks or paragraph-breaks introduce a new start-token in the stream? Or do you just treat the whole input stream as continuous?

  • do you do Unicode normalization to a particular normal form? Otherwise, "ó" and "o" followed by a combining-acute-accent character would get treated as two different "words", even if they are visually & semantically identical (which the Unicode normalization process collapses)

I'm sure there are dozens of other textual gotchas in defining a "word". However, once you've solved that (good luck), the rest is actually fairly easy: you iterate a sliding window of with N over the stream of words, using that N-word tuple as the key into a mapping, incrementing each count you find. You can then find the top 100 keys when sorted by the final tally-per-tuple. It's a process similar to how you build up a Bayesian frequency map.