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 |
|
tollieb
Starting Member
3 Posts |
 Posted - 2007-10-29 : 19:06:08
|
| My database has a gigantic text field that is used for comments that I need to break up in to individual records. When a user adds a comment, the current date/time is entered, and then they type the information. So, I need to a query/function that will run through each text field, and break each date/time + information in to individual records in another table. For example, a record that has 10 entries (10 date/time + information) needs to be broken up in to 10 individual rows. Using each date/time as a delimiter, Is this possible? |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-10-29 : 19:11:45
|
| There is no other character in the field that would be considered a delimiter?YOu are going to have to do this in stages, parsing out the text into multiple COLUMNS first (if that is even possible, given the lack of a steady delimiter).Ideally you would have a delimiter of SOME kind before the date and new comment, if there is a delimiter before the date (and no, datetime can't be the delimiter--it has to be something repeated like a tab or comma). Are the little squares at the end of the comments or some other mystery symbles that might be a delimiter?I would smack whoever designed the structure that way, rather than just having multiple rows to begin with in a separate table. |
 |
|
|
tollieb
Starting Member
3 Posts |
Posted - 2007-10-29 : 19:41:33
|
| Dataguru, the only thing that could be considered a consistent delimiter between the individual text packets is a hard return. That makes things easier/possible?I'll order up a smack for the database manager first thing. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-10-29 : 19:51:07
|
| Just seems like a bad way to store the data...If a carriage return is stored in the field (aka a hard return) that can be used as a delimiter. Is it visible in the text field itself? Usually it looks like a little square.If so, there is a possible way to split the column out by first replacing that character using an update / replace T-SQL statement. I would replace it with a vertical bar or something more common as a delimiter and then use DTS to split that column into it's own table. You would probably have to find out the max number of comments in one of these fields and pre-build a table with enough columns for this to work.Don't smack your manager to hard, may not have been his idea. Someone thought it was a good idea at some point though.if you want, send me a sample of the text and I can help you work something out...(maybe). |
|
|
|
tollieb
Starting Member
3 Posts |
Posted - 2007-10-30 : 11:00:59
|
| No symbols to speak of for the carriage returns. To be honest, not even sure how the interface interprets the returns (need to talk to developer when they're in, perhaps). In the interface, where users type the comments, here's an example of the text:8/22/2006, 9:34:43 AM - Sent login, p/w, F&B's list and price quote.8/22/2006, 9:21:45 AM - FD referred them They use billing system. has 1 additional garage site. 8/22/2006, 8:55:54 AM - No response.8/21/2006, 2:01:50 PM - Not in Sent emailIn the database, the same text appears like this in the text field:8/22/2006, 9:34:43 AM - Sent login, p/w, F&B's list and price quote. 8/22/2006, 9:21:45 AM - FD referred them They use billing system. has 1 additional garage site. 8/22/2006, 8:55:54 AM - No response. 8/21/2006, 2:01:50 PM - Not in Sent email Thanks for any help, even if it's to fully identify this isn't possible. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-30 : 11:20:45
|
| "I'll order up a smack for the database manager first thing"Hehehe ... Good Luck with that!You can split the data from the TEXT column into multiple rows using a "split" function.For starters see: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Best%20split%20functionsYou may get stuck because the column has large text in it, but under SQL2005 you should be able to use varchar(MAX) for the parameter to the SPLIT function; either way, come back if you get stuck.Kristen |
|
|
|
|
|
|
|
|
Breaking text in to multiple records