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.
2 Answers
;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
2 Comments
asma
I dnt know exactly that values will be AXISIII,AXISI,AXISII,AXIS-C or some else. I just need to replace AXIS value with AXISIII
Mikael Eriksson
@asma – this query does exactly that. The
;with DIAGNOSIS.. statement is just some sample data that I used to test on. You should only use the select statement against your table.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
4 Comments
asma
I want all the records remain same but when AXIS come, it must become AXISIII
asma
if I use this:
WHEN 'AXIS' THEN 'AXISIII' what about the rest of the values? like AXISI, AXISII etc? I want to select those as well unconditionally.
Eystein Bye
You just write... SELECT CASE(diag_type) WHEN 'AXIS' THEN 'AXISIII' ELSE diag_Type END FROM DIAGNOSIS
asma
yeah you were right.. I wish I could mark two answers at a time :(
REPLACE(diag_type, 'AXIS', 'AXISIII')but I need to replace just AXIS.. Not AXISII or AXISIV or any other AXIS