1

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?

Improve this question
1
  • I'm not an expert at all but ... appending/concatenating multiple values in one column like u're trying to do doesn't seem to be very "database"-style ... (imho)
    – Snaileater
    May 3, 2018 at 14:13

1 Answer 1

Reset to default
2

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...

Improve this answer
2
  • Elegant isn't important just now! Thanks very much, work perfectly.
    – Graham04
    May 4, 2018 at 7:25
  • @Graham04 You're welcome! If this answer helped you, please mark it as "accepted" by clicking the green checkmark next to the answer score.
    – ArMoraer
    May 4, 2018 at 9:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.