Double REPLACE on same field in SQL

Posted on 2007-07-23
Medium Priority
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

Sham Haque earned 1000 total points
ID: 19549075
you can nest your REPLACEs:

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


Expert Comment

ID: 19549094
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
Aneesh Retnakaran earned 1000 total points
ID: 19549152
SUBSTRING(RiskSignificance, CHARINDEX(' ',RiskSignificance),PATINDEX('%Risk%',RiskSignificance)-CHARINDEX(' ',RiskSignificance))

Author Comment

ID: 19549224
Thanks, both solutions worked.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

755 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