Check is this fits your need:
SELECT DISTINCT
NDS = CASE
WHEN TRIM(STG.CODECHECK) = 'MILT' THEN 'MILLER'
WHEN TRIM(STG.CODECHECK) = 'ROMB' THEN 'ROMAN BOWLER'
WHEN TRIM(STG.CODECHECK) = 'BANK' THEN 'LOANER'
WHEN TRIM(STG.CODECHECK) = 'MORT' THEN 'MOVABLES'
-- Add more condition as needed here for other cases
END
, TRIM(STG.OPTCODE) AS SRC_CD
, UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION
, TRIM(STG.OPTCODE) AS CON_CODE
, UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC
, 'HEALTHCARE' AS DOM_NAME
FROM MYCheck1Temp1 STG
WHERE STG.VALID_RECORD = 'Y' AND SRCCODE <> ''
AND TRIM(STG.CODECHECK) in ('MILT', 'ROMB', 'BANK', 'MORT') -- add more option in the IN condition as needed
Note! VERY IMPORTANT! You should have the right indexes according to the filter conditions but using condition on TRIM(STG.CODECHECK) in the where part might reduce the performance dramatically since the server might not be able to use the index on STG.CODECHECK. It is better to store the data after it is trimmed if this fit your case.
Check this sample and why it is not a good idea to use filter on a column that you manipulate
create table T(id int identity(2,2), txt nvarchar(100))
insert T(txt) values (' ronen '),('ronen'),(' ronen')
CREATE CLUSTERED INDEX i1 ON T (id);
CREATE nonCLUSTERED INDEX i2 ON T (txt);
-- this scan all the index since the server must first execute TRIM on the value before it can filter by it
select txt from T where TRIM(txt) = 'ronen'
-- this use the index seek which is very useful, since here the server could filter according to the index
select txt from T where txt = 'ronen'