Looking for a way to return a table with a row for every word contained in a column in another table

Question

Looking for a way to return a table with a row for every word contained in a column in another table

Stephen Phayre

SSC Veteran

Points: 299
More actions
September 16, 2011 at 5:42 pm

#97926

I'm looking to rank words contained in a varchar column in a table. I would like to create an output table that contains a row for every word in the column.
I would also like to run some other queries on the output table.
Table1
Identifier
Title (varchar)
Table2 (Desired output)
Table1.Identifier
Word
For every word in the [Title] column, I would like a single row in Table2 along with the Identifier from Table1.
So if Table1 contained the following:
1|This is a test
2|This is more text
3|Hello world
The output (Table2) would be:
1|This
1|is
1|a
1|test
2|This
2|is
2|more
2|text
3|Hello
3|world
I could do this using some ugly row by row code, but was wondering if I'm missing something simple...

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Unfortunately, there are no sample data in your post so we're left with guessing what you're looking for.

My guess: it sounds like a split string function. Search this site for DelimitedSplit8K to find one of the best performing functions to do that.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Stephen Phayre SSC Veteran Points: 299 More actions · Answer 2

Thanks for the reminder to add in sample data. I've edited the post to include what I'm looking for.

LutzM SSC Guru Points: 107049 More actions · Answer 3

What did your search for the split function return? 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Stephen Phayre SSC Veteran Points: 299 More actions · Answer 4

From what I can see it is not to work with SQL 2000 - but I've only started playing with the code from my search 🙂

Stephen Phayre SSC Veteran Points: 299 More actions · Answer 5

I ended up doing this with a cursor. NOT the best way I'm sure but there isn't much I could do as I needed to produce a result quickly.