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 |
|
lane0618
Posting Yak Master
134 Posts |
 Posted - 2005-07-25 : 17:49:43
|
| I have a table full of note fields like the one below. the format of the field is <dateTime><-><the note><[username]>. I need to parse this field into 3 field (date/time, note, username). As you can see from the example below, there are multiple note entries, so I need not only to parse the text into 3 fields, but also seperate the notes into muliple records. Thanks!CommentsMain================Thu, 7/21/2005, 1:10 pm - my note 1 [KMcKarthur] Tue, 7/12/2005, 9:40 am - my notes 2 [acamp] Mon, 7/11/2005, 5:36 pm - my notes 3 [wclark]should look like:entryDate / note / userNameThu, 7/21/2005, 1:10 pm / my note 1 / KMcKarthurTue, 7/12/2005, 9:40 am / my notes 2 / acampMon, 7/11/2005, 5:36 pm / my notes 3 / wclark |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2005-07-25 : 19:04:48
|
| There are several solutions to this problem. The one you select will depend on a)how often it will run b)how it will be executed and c)how many records would there be to process.If it was a once off I would tend to write a quick bit of VBScript or a small VB app to do it. Or you could roll it into a DTS package so it's reusable. But if you want to run it multiple times and performance is an issue, I would write a stored procedure to do it. All you need to do is create a loop that reads each note in sequence (use a cursor if you must but there are better ways), delimit it out into its component parts then creates the normalised records.Have a shot at doing this yourself. If you get stuck someone here is sure to help you. Tim |
 |
|
|
|
|
|
|
|
Parsing text field