asked
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.
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
@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.
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-
@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)?
@SirSQL Well several methods turn the whitespace and punctuation into a single delimiter and then turn it into a string-splitting problem. You're part-way there, but what about full-stops, semicolons and colons? It is beginning to sound like a regex problem!
9 People are following this question.
