Best way of splitting text into its individual words in SQL

Question

question

Phil Factor asked Sep 12, '12

Best way of splitting text into its individual words in SQL

I know plenty of ways of splitting strings, but what is the best/fastest way of splitting text into its individual words to produce a table of words, and their sequence? Naturally I'd want to take off trailing punctuation but preserve it if it was within the word. The reason I'm interested is that I want to run some timings to check performance. What I'm writing is a system for doing an inversion index for searching and gauging text similarity. The last time I did it, it was a bit slow.

string split string-splitting words

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2012-09-12T16:37:52Z

SirSQL answered Sep 12, '12

Aaron Bertrand did an awesome write up on doing this, along with performance comparisons. Check out [ http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql][1] [ http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings][2] and [ http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up][3] [1]: http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql [2]: http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings [3]: http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up

6

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AaronBertrand commented · Sep 12, 2012 at 05:51 PM

@Phil that makes things quite tricky, as you can imagine. I can show you how to split the string in T-SQL and maintain order in the output, but I would say you are never going to be able to solve this problem in T-SQL as easily *or* to run as efficiently as you could do it in CLR, where you have the added benefit of native RegEx to deal with punctuation etc.

2 ·

Phil Factor commented · Sep 12, 2012 at 04:52 PM

Oh Dear. I can't quite see how this relates to the problem of splitting out words from text. Text is delimited by whitespace, which includes one or more of a range of characters, and may have trailing punctuation which is treated as whitespace. The text I've just typed in should return a table that begins.. Word --------- Oh Dear I Can't Quite See -etc-

0 ·

SirSQL commented · Sep 12, 2012 at 04:57 PM

Could you not take the string, remove any non-alpha, replace the commas and run it through one of those, or have them run with spaces instead of commas (I've not done this, so spitballing a little here).

0 ·

AaronBertrand commented · Sep 12, 2012 at 05:44 PM

@Phil did you intentionally remove the ' from "can't", e.g. you don't care about punctuation even if it's in the middle of a word or name (like O'Shea)?

0 ·

Phil Factor commented · Sep 12, 2012 at 05:49 PM

@Aaron It was a mistake. I've corrected it

0 ·

Show more comments

question