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

Posted on 2011-10-20
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
    LVL 39

    Accepted Solution

    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

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

    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now