Split out of data

Question

Split out of data

TRACEY-320982

SSChampion

Points: 13653
More actions
April 24, 2011 at 7:40 am

#249328

I have a database that has a column of Text
Table
column1 (Type of Object) nvarchar(22)
column2 (Type of Error Code) nvarchar(22)
column3 (Data) text
Data consists of [Error] this is the first error [Error] this is the second
Trying to get it to return in rows
column1, column2 column3
program1, check , this is the first error
program1, check, this is the second error
Somes data is 100 characters others may have 1800 errors.
Any ideas of how to split the data out.

Viewing 7 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

please provide table def and ready to use sample data in the form of INSERT ... SELECT as well as your expected result. Otherwise we just have to guess the table structure. it's possible that a pslit string function might be the solution, but that's hard to tell at the moment.

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]

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 2

CREATE TABLE [dbo].[TABLE1](

[ID] [nvarchar](22) NOT NULL,

[CONTEXT] [nvarchar](30) NOT NULL,

[VALUE_ID] [nvarchar](22) NOT NULL,

[PROCESS_ID] [nvarchar](59) NOT NULL,

[DATA] [text] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ID = '1'

CONTEXT = 'Project'

VALUE_ID = '1000'

PROCESS_ID = 'Check'

DATA = 'Process Name: Check User: Sor Start Time: 04/20/2011 03:51:07 PM Project: 1000 Check. [Error]CA (open) Invalid Forecast [Error]WP (open) / 10000 / Invalid Process completed. Finish time: 04/20/2011 03:51:31 PM Errors: 21 Warnings: 0'

Expected Output

CONTEXT, VALID_ID, PROCESS_ID, DATA

Project 1000 Check [Error]CA (open) Invalid Forecast

Project 1000 Check [Error]WP (open) / 10000 / Invalid '

Insert

declare @text as text

set @text = 'Process Name: Check User: Sor Start Time: 04/20/2011 03:51:07 PM Project: 1000 Check. [Error]CA (open) Invalid Forecast [Error]WP (open) / 10000 / Invalid Process completed. Finish time: 04/20/2011 03:51:31 PM Errors: 21 Warnings: 0 '

INSERT INTO TABLE1 (CONTEXT,VALUE_ID, PROCESS_ID, DATA)

SELECT 'Project','1000','Check',@text

(Insert not work said text, nxtext are invalid or local variables

Size of my datatype TEXT column is 1319433

WayneS SSC Guru Points: 95460 More actions · Answer 3

This will get you the different errors. The issue is that the last error has additional information after the error message - you'll have to figure out how to remove that.

The latest DelimitedSplit8K function is linked to in my signature.

SELECT *

FROM #table1 t1

CROSS APPLY dbo.DelimitedSplit8K(REPLACE(CONVERT(VARCHAR(max),t1.data), '[Error]', '|'), '|') ds

WHERE ds.ItemNumber > 1

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 4

Worked on my small sample of data, now running on my hugh output.

Thank you for your time and supplying code.

WayneS SSC Guru Points: 95460 More actions · Answer 5

Thanks for the feedback.

How did you handle the apparently extra stuff at the end of the last error message?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 6

The last part at the end wasn't of much use for the users just the Error detail so left that in there.