How to use REPLACE in Select Query? (SQL SERVER)

Can I use REPLACE function conditionally? e.g. I have a query:

SELECT diag_开发者_运维知识库type FROM DIAGNOSIS

It returns AXIS, AXISI, AXISII, AXIS-C, etc. I want if it returns a result that has AXIS, it makes it AXISIII. Can it be possible?

Thanks in advance.

---

;with DIAGNOSIS(diag_type) as
(
  select 'AXIS' union all
  select 'AXISI' union all
  select 'AXISII' union all
  select 'AXIS-C'
)

select
  case diag_type
    when 'AXIS' then 'AXISIII'
    else diag_type
  end as diag_type
from DIAGNOSIS

Result:

diag_type
---------
AXISIII
AXISI
AXISII
AXIS-C

---

If all you want is it to return AXISIII you can just write:

SELECT 'AXISIII' FROM DIAGNOSIS WHERE diag_type = 'AXIS'

You can also use a CASE-statement to add more options (if you need).

SELECT  CASE(diag_type)
    WHEN 'AXIS' THEN 'AXISIII'
    WHEN 'AXIS-C' THEN'AXISIII-C'
    WHEN 'AXISII' THEN'AXISII'
    ELSE    'Something'
    END
FROM DIAGNOSIS