Translating strings postgres

Question

I'm trying to translate 2 types of data using only postgres SQL I've got a column "type" that may contains the kind of data.

type is a string column and may have "ACTUAL" or "OLD" values

+-type-+
+ACTUAL+
+OLD   +
+------+

when I show the list with a lot of other joins I would like to show only "A" or "O" values I couldnt find other way to do this than:

SELECT replace(replace(mytable.type, 'ACTUAL', 'A'),'OLD', 'O');

With that I can replace the text the way I need, but I was looking for some more function using an array as parameter.

something like a cross-reference simple function:

translate(['ACTUAL','OLD'], ['A','O'])

Does anyone know a way to do this that doesn't use SQL views and neither needs another table like joining the results of this value with other table?

Thanks in advance, Andre

Chris Cashwell · Accepted Answer · 2011-09-23 20:18:51Z

3

I would use something like CASE...

SELECT
  (CASE type 
   WHEN 'ACTUAL' THEN 'A' 
   WHEN 'OTHER' THEN 'O' 
   ELSE '?' END) 
FROM

Using this method, you can return whatever you want based on whatever criteria you want, not just sub-stringing.

answered Sep 23, 2011 at 20:18

Chris Cashwell

23k13 gold badges67 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1