This is a handy feature that has been in the Firebird database system a while (but I just learned about).
I needed to replace certain results in my select query. I use a SQL query to generate an Excel file that I upload to a stock listing service for aircraft parts. The problem is, this stock listing service does not like certain part condition codes. So I need to replace all instances of RP with SV.
So I use a query like this:
select stock.pn, case condition_code when 'RP' then 'SV' else condition_code end condition_code from stock inner join part_condition_codes on (stock.pcc_auto_key=part_condition_codes.pcc_auto_key) where STOCK.IFC_AUTO_KEY=4;
What this does is if the “condition_code” is RP then it changes it to SV. If it is anything else it just uses whatever that value is. The “condition_code” after “end” is not necessary – I wanted the field to be named condition_code.
You can add multiple “when …” clauses.
And of course RTFM! Here is the page in the manual: