Concatentate nvarchar(1024) field for text mining

Question

Concatentate nvarchar(1024) field for text mining

anish_ns

SSCommitted

Points: 1705
More actions
March 9, 2010 at 4:16 pm

#219775

Greetings!
I have a table which is primarily used for chats and stores chats as text segments with a many-to-one relationship with a uniqie session identifier. A sample of the table is below
SESSIONID TEXT
------------------
2751446 Thanks Swiss, I may be chatting to you again soon. Enjoy your day!
2751446 Visitor XXX has ended the chat.
2751467 I have to walk away in about 10 seconds
2751467 please hit enter now
2751467 The chat has ended
SESSIONID (int)
TEXT (NVARCHAR(1024))
We have about 74,216 uniqie SESSIONID's and 1.5 million total records in the table. Now, we want to so some text mining on the data. So, I would like to create a text file for each unique sessionID with all associated text data. i.e.
2751446.txt whose contents should be:
Thanks Swiss, I may be chatting to you again soon. Enjoy your day!
Visitor XXX has ended the chat.
Notice, that the records are separated by a 'newline' character. Can anyone please help me in doing that. Its only one table that I need to worry about.
Thanks in anticipation.
Anish S.
I would like to create a text file for each unique session ID

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

anish_ns (3/9/2010)
...
Now, we want to so some text mining on the data. So, I would like to create a text file for each unique sessionID with all associated text data. i.e.
...

Why would you store the data in a text file to do text mining? Wouldn't SQL Servers full-text search be more efficient? Just think about it...

Regarding your request to export the data:

There seems to be one column missing to define the order of each text row...

Other than that, I guess either SSIS or bcp will do the job.

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]

anish_ns SSCommitted Points: 1705 More actions · Answer 2

Thanks LMU

There is a datetime field which I can use to order by.

I am still a layman as far as full-text search is concerned for text mining. Can you point me in the right direction?

Also, any help on SSIS would be helpful, I have to do something similar with a MySQL database

Cheers!

lmu92 (3/9/2010)
anish_ns (3/9/2010)
...
Now, we want to so some text mining on the data. So, I would like to create a text file for each unique sessionID with all associated text data. i.e.
...
Why would you store the data in a text file to do text mining? Wouldn't SQL Servers full-text search be more efficient? Just think about it...
Regarding your request to export the data:
There seems to be one column missing to define the order of each text row...
Other than that, I guess either SSIS or bcp will do the job.

LutzM SSC Guru Points: 107049 More actions · Answer 3

I am still a layman as far as full-text search is concerned for text mining.

It depends on how you want to deal with your data. Or, in other words: what's your definition of text mining? Do you plan to use any kind of software to analyse your text data or are you planning to develop our own?

You might want to google for "text mining sql server" as a start...

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]