|
Subject:
|
Parsing a String?
|
|
Posted By:
|
MBowen
|
Post Date:
|
11/30/2006 2:14:47 PM
|
I have a colum with a huge amount of data inside of it. There is only maybe one or two parts to it that I need.
How can I take this and just return the basic data that I need rather than all of it?
Example String in column:
<Subdialog_Start_01>null<checkflowStatus>null<Greeting>null<language_selection_menu>EN<setinvalidcount>null<checkInvalidCount>null<invalidMsg>null<language_selection_menu>EN<setLanguage>null<ancmtFlag>null<checkLan>null<provORParticipant>1<provORParticipan
out of this all I need is <language_selection_menu>EN
Thank you!
Margot
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
11/30/2006 2:41:17 PM
|
How many times? The string you provided contains <language_selection_menu>EN multiple times...
--Jeff Moden
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
11/30/2006 2:46:37 PM
|
If you are trying to detect ANY occurance, then this will work...
DECLARE @LongString VARCHAR(8000)
SET @LongString = '<Subdialog_Start_01>null<checkflowStatus>null<Greeting>null<language_selection_menu>EN<setinvalidcount>null<checkInvalidCount>null<invalidMsg>null<language_selection_menu>EN<setLanguage>null<ancmtFlag>null<checkLan>null<provORParticipant>1<provORParticipan'
DECLARE @ToFind VARCHAR(8000)
SET @ToFind = '<language_selection_menu>'
DECLARE @MyLength INT
SET @MyLength = LEN(@ToFind)
SELECT CASE CHARINDEX(@ToFind,@LongString)
WHEN 0 THEN NULL --If not present at all
ELSE SUBSTRING(@LongString,CHARINDEX(@ToFind,@LongString)+@MyLength,2)
END
--Jeff Moden
|
|
Reply By:
|
MBowen
|
Reply Date:
|
11/30/2006 3:04:42 PM
|
Sorry - didn't realize that it posted more than once I just pulled it as an example. In the end it will be 1 unique feild per line. So your second posting might work. Will try it now to see.
Thanks,
Margot
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
11/30/2006 3:59:32 PM
|
Margot... did you get this from an XML file? The reason I ask is that SQL Server does have some pretty good XML handling functionality...
--Jeff Moden
|
|
Reply By:
|
MBowen
|
Reply Date:
|
11/30/2006 4:33:06 PM
|
No, it was straight out of a SQL 2000 database table. 1 specific column, however, I am doing some more reading. For what my end results need to be I have a pretty heavy task to get the data results I want.
I will have to find the location within the string of each line for the item that I need first, then I need to test to see if its in the date frame more than 1 time, returning results that can show how many times a unique record id comes thru the database in a three month time frame.
So if I have a user id that is in the string, it could be 100 characters in or more than 100 characters in. (Each line could be unique or a few could be exactly alike.) And so far, I am returning over 200,000 rows.
Margot
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
11/30/2006 5:24:57 PM
|
What does the Primary Key of the table look like? I'm thinking I might be able to pull off a little magic for you...
--Jeff Moden
|
|
Reply By:
|
MBowen
|
Reply Date:
|
12/1/2006 8:15:33 AM
|
Primary Key is the TimeStamp field with datetime variables going to the millisecond.
Margot
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
12/1/2006 12:02:02 PM
|
Let's just say that the table the data lives in looks like....
[Date] DATETIME PRIMARY KEY,
LongString VARCHAR(8000)
This simple bit of code will split out all of the data element names and data in a format that could be used as a derived table or inserted into a temp table or even into a permanent table....
SELECT d.[Date],
NULLIF(d.Element,'null') AS Element,
SUBSTRING(d.ElementData,0,CHARINDEX('<',d,ElementData)) AS ElementDate
FROM
(
SELECT y.Date,
SUBSTRING(y.LongString,t.N,CHARINDEX('>',y.LongString,t.N+1)-t.N+1) AS Element,
SUBSTRING(y.LongString,t.N+CHARINDEX('>',y.LongString,t.N+1)-t.N+1,8000)+'<' AS ElementData
FROM yourtable y, Tally t
WHERE SUBSTRING(y.LongString,N,1) = '<'
AND t.N <= LEN(y.LongString)
)d
Of course, you will also need the "Tally" table... has lot's of other uses, as well... here's the code to make one...
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
It will not discriminate against any dupe tags that may show up in LongString.
BTW, the example long string you gave me very much looks like some poorly formed HTML or maybe XML.
--Jeff Moden
|
|
Reply By:
|
MBowen
|
Reply Date:
|
12/1/2006 12:17:59 PM
|
Wow! You rock!
I will work with it this afternoon to see my results. Will let you know what happens!
Thanks!
Margot
|
