Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gsashwin
Starting Member
2 Posts |
 Posted - 2012-02-05 : 01:12:57
|
| Hi All,I am trying to do text mining on a column so basically looking for a set of words within a column. However I want to have an additional concept within the text mining; Whenever the query finds a matching word in a record it should create a new column and put the matched word corresponding to the record in the new column and if it finds a second record having the matching word it should again put the matching word in the column that's been created earlier. The way I am running text mining is like this...Select Col A, Col B FROM [dbo].[Logs_Q3] WHERE CONTAINS([COL A], ' "Battery" OR "Power" OR "Hard Disk" Here's what I want; Once the query finds Battery/Power/Hard Disk in COL A it should create a new column and mention whatever word it found: Battery/Power/Hard Disk corresponding to the row it found the word in the new column...For all subsequent matches just make an entry to those rows in the new column...I hope I made sense...Thank you in advance to everyone.. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-05 : 08:42:42
|
| Creating columns in response to data in this manner is not a good idea for several reasons: a) it is not scalable, b) you would find it hard to answer questions such as "give me a list of search terms that were found", c) it would be violating some rule or other that Codd suggested etc.Instead, you could have a reference table that has the search terms, along with an identifier column. Then, you would have a link table that lists the id from your table to be search and the id from the reference table.That is my initial thought. Some of the others on the forum may be able to suggest even better ways of doing it. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-05 : 10:37:36
|
| I second Sunita on this. You should be using a link table concept as its scalable. You can keep on adding as many references for texts as you want and also matching words against them. Still, if you want to show them as columns you can apply cross tabbing logic to get the words for text in different columns.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Text mining