extracting specific data from a text string in a column field in sql server 2005

Posted on 2011-10-20
Medium Priority
Last Modified: 2012-05-12
I have a column in a sql server 2005 database table which typically contains the following text;
severity <WARNING> status <OPEN> event_class <ITO_WIN2K> resource_id <> appl_id <> hostname <uk123>

I want to create a  query that extracts , in the above example, the “event_class” entry only, which in this case would be “ITO_WIN2K”
The text in the column fields may not always follow the pattern above, but each filed should contain an “event_class” followed by <some data>

any help appreciated, thanks
Question by:blossompark
  • 2
LVL 39

Accepted Solution

Pratima Pharande earned 2000 total points
ID: 36998525
little complecated but
surely it works for your cases

declare @str varchar(200)

set @str = '<WARNING> status <OPEN> event_class <ITO_WIN2K> resource_id <> appl_id <> hostname <uk123>'

select @str, Substring (Substring (@str,CharIndex('event_class',@str)+11,Len(@str)),CharIndex('<',Substring (@str,CharIndex('event_class',@str)+11,Len(@str)))+1, CharIndex('>',Substring (@str,CharIndex('event_class',@str)+11,Len(@str)))-CharIndex('<',Substring (@str,CharIndex('event_class',@str)+11,Len(@str)))-1)

Xhange @str with your column name

Author Comment

ID: 36998666
Hi pratima mcs, thanks for that...will try out  and update you...thanks again

Author Closing Comment

ID: 36999005
Hi pratima mcs
as usual 100% perfect solution!!
thank you....again!!

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question