Double REPLACE on same field in SQL

Posted on 2007-07-23
Last Modified: 2010-05-18
I'm using a SQL query to populate an excel spreadsheet.  One of my fields comes back as this:

Four High Risk Exposure
Three Medium Risk Exposure
Two Low Risk Exposure

I need it to read High, Medium or Low.  I've got a REPLACE query running on it, but thtat will only take out the beginning or the last part.  Bit stumped as to perfoming a double replace or trimming.  This is my SQL query I'm currently running:

SELECT      Risk.RiskTitle, OrganizationCycleMap.OrganizationElement, Risk.RiskType, REPLACE(Risk.RiskSignificance, ' Risk Exposure','') , Risk.RiskRating, Risk.RiskDescription, Controls.Control_Ref
FROM         xxxxx

But that leaves it as Four High, Three Medium, Two Low.  

Any clues?
Question by:ITmonkeys
    LVL 18

    Accepted Solution

    you can nest your REPLACEs:

    REPLACE(REPLACE(REPLACE(REPLACE(Risk.RiskSignificance, ' Risk Exposure','') ,'Four ',''),'Three',''),'Two','')

    LVL 8

    Expert Comment

    SELECT      Risk.RiskTitle, OrganizationCycleMap.OrganizationElement, Risk.RiskType, right( column1, charindex(' ',REPLACE(Risk.RiskSignificance, ' Risk Exposure',''))), Risk.RiskRating, Risk.RiskDescription, Controls.Control_Ref
    FROM         xxxxx

    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    SUBSTRING(RiskSignificance, CHARINDEX(' ',RiskSignificance),PATINDEX('%Risk%',RiskSignificance)-CHARINDEX(' ',RiskSignificance))

    Author Comment

    Thanks, both solutions worked.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    Viewers will learn how the fundamental information of how to create a table.

    729 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

    18 Experts available now in Live!

    Get 1:1 Help Now