Multiple Field Update on comma separated values

Question

I am renaming data within columns from names to numbered codes to prepare for importing into a database. This is easy enough if I have single entries within the field, but I'm struggling to update multiple comma separated values. For single entries, the below example works fine in field calculator:

case when "Enviro" = 'A'
then '118'
when "Enviro" = 'B'
then '119'
when "Enviro" ILIKE '%C%'
then '120'
when "Enviro" ILIKE '%D%'
then '121'
when "Enviro" ILIKE '%E%'
then '122'
when "Enviro" ILIKE '%F%'
then '123'
end

However if the "Enviro" field contains both A and B for example (A,B) then the code for 'B' will automatically overwrite any reference to 'A' in the final output. Ideally, I'd want this particular example to read 118,119 in the final version. Any ideas?

Answer 1

Not very elegant, but you can try something like:

replace( 
  replace( 
    replace(
      replace("Enviro", 'D', 121),
    'C','120'),
  'B', '119'),
'A', '118')

and so on...