Can you replace a REPLACE statement, or 9?

I would really like something to replace all of these REPLACE Statements.

SELECT DISTINCT
CAH.CaseNbr
    , REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            (SELECT table1.Degree 
            FROM SDUJS.dbo.fnWorstDegreeCharge(CB.CaseID) AS table1), 'F1', 'F')
            ,'F2','F'),'F3','F'),'F4','F'),'F5','F'),'F6','F'),'FA','F'),'FB','F')
            ,'FC','F') AS Degree
    , dbo.fncasecounty(CB.CaseID) AS County
FROM
    Justice.dbo.CaseBase AS CB
    INNER JOIN Justice.dbo.xCaseBaseChrg AS xCBC WITH (NOLOCK) ON xCBC.CaseID = CB.CaseID
    INNER JOIN Justice.dbo.Chrg AS Chrg WITH (NOLOCK) ON Chrg.ChargeID = xCBC.ChargeID
    INNER JOIN Justice.dbo.CaseAssignHist AS CAH WITH (NOLOCK) ON CAH.CaseID = CB.CaseID
    INNER JOIN Justice.dbo.ClkCaseHdr AS CCH WITH (NOLOCK) ON CCH.CaseID = CB.CaseID
WHERE 
    CCH.DtFile BETWEEN '07/01/2010' AND '06/30/2011'
    AND 
    CCH.CaseUTypeID IN (858,4330,865,4329,6362,5112)
ORDER BY CaseNbr

All possible entries for that column


M1, M2, MO, PO, CHINS, F1, F2, F3, F4, F5, F6, FA, FB, FC, 00

I want all the Felonies to be represented by F

Code after the review

I decided not to do this as a follow up question because there isn't much that I think can be done to this query now to make it faster, except for get rid of the DISTINCT which I don't feel comfortable doing right now.

SELECT DISTINCT
    CAH.CaseNbr
    , (SELECT table1.Degree FROM SDUJS.dbo.fnWorstDegreeCharge(CB.CaseID) AS table1) AS Degree
    , dbo.fncasecounty(CB.CaseID) AS County
FROM
    Justice.dbo.CaseBase AS CB
    INNER JOIN Justice.dbo.CaseAssignHist AS CAH WITH (NOLOCK) ON CAH.CaseID = CB.CaseID
    INNER JOIN Justice.dbo.ClkCaseHdr AS CCH WITH (NOLOCK) ON CCH.CaseID = CB.CaseID
WHERE 
    CCH.DtFile BETWEEN '07/01/2010' AND '06/30/2011'
    AND 
    CCH.CaseUTypeID IN (858,4330,865,4329,6362,5112)

Missing REPLACE statement

I moved the "REPLACE" to the function that I am calling, but I

This strikes me as best being done by using a temp table, or with clause.

Also, the DISTINCT part worries me.... why is that needed? Are your joins not good?

Still, I think you may have some more contentment with:

SELECT DISTINCT
    CAH.CaseNbr,
    (SELECT CASE WHEN Degree in ('F1','F2','F3','F4','F5','F6','FA','FB','FC')
                 THEN 'F'
                 ELSE Degree
            END
     FROM SDUJS.dbo.fnWorstDegreeCharge(CB.CaseID)) AS Degree,
  dbo.fncasecounty(CB.CaseID) AS County
....

You should still consider a better mechanism for joining. The procedure route seems 'klunky'

StackExchange Code Review Q#39828, answer score: 6