# 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 used @rolfl's wonderful Case Statement idea to get rid of all the REPLACE statements inside that function.

-
Are there, or can there be, other values starting with 'F' that would be broken if you selected the first leftmost character instead? It might be useful to include a few possible values of that field. –  Mat's Mug Jan 22 '14 at 19:38
Warning: with nolock is dangerous: stackoverflow.com/a/5469238 –  Chris Mathews Jan 24 '14 at 17:43
@ChrisMathews, this is something that the Vendor told us to do, in order to speed things up. I am pulling data from a report server. if I put a lock on all these tables then I can't run concurrent reports, this can cause issues. that is the purpose of using NOLOCK but thank you for that information. –  Malachi Jan 24 '14 at 17:51
@ChrisMathews, in other words there shouldn't be too much going on in terms of things being changed here, so the dangers are less than what you are thinking because of the way that the Databases are set up. –  Malachi Jan 24 '14 at 17:52

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'

-
it's a really bad Data Retrieval (PITA Report). I am labeling a case with one Charge's degree, a case can have many charges and they can all be different degrees. the data is good but what they want isn't always good for the data... does that even make sense? –  Malachi Jan 22 '14 at 22:17
the database tables are connected all over the place so I could end up with duplicated data. I almost always throw it in there on this Database. I probably don't need it on this query because it isn't bringing back more than a couple of columns –  Malachi Jan 22 '14 at 22:33
I like the CASE statement. I will see if I need the DISTINCT or not while at work today. –  Malachi Jan 23 '14 at 13:47
I will wait until the Question is Eligible for a bounty and then accept your answer. I need the DISTINCT` I am either not as good of a SQL Dev that I need to be, or the Database is really screwy....or both. I can't believe that I didn't think of a Case Statement...Sigh. –  Malachi Jan 23 '14 at 22:47
That's why CodeReview is a Good Thing™ .... pulls on different people's eyes, experience, and opinions. –  rolfl Jan 23 '14 at 22:49