Parsing a String?

MBowen · November 30th, 2006, 03:14 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<Gree ting>null<language_selection_menu>EN<setinvalidcou nt>null<checkInvalidCount>null<invalidMsg>null<lan guage_selection_menu>EN<setLanguage>null<ancmtFlag >null<checkLan>null<provORParticipant>1<provORPart icipan

out of this all I need is <language_selection_menu>EN

Thank you!

Margot

Jeff Moden · November 30th, 2006, 03:41 PM

How many times? The string you provided contains <language_selection_menu>EN multiple times...

--Jeff Moden

Jeff Moden · November 30th, 2006, 03:46 PM

If you are trying to detect ANY occurance, then this will work...

Code:

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

MBowen · November 30th, 2006, 04:04 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

Jeff Moden · November 30th, 2006, 04:59 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

MBowen · November 30th, 2006, 05:33 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

Jeff Moden · November 30th, 2006, 06:24 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

MBowen · December 1st, 2006, 09:15 AM

Primary Key is the TimeStamp field with datetime variables going to the millisecond.

Margot

Jeff Moden · December 1st, 2006, 01:02 PM

Let's just say that the table the data lives in looks like....

Code:

[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....

Code:

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

Code:

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

MBowen · December 1st, 2006, 01:17 PM

Wow! You rock!
I will work with it this afternoon to see my results. Will let you know what happens!

Thanks!
:D

Margot