CODEWITHSUNDEEP
stringpostgresqltranslation
AndreDurao
AndreDurao

Reputation: 5795

Translating strings postgres

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

Upvotes: 0

Views: 1021

Answers (1)

Chris Cashwell
Chris Cashwell

Reputation: 22899

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.

Upvotes: 3

Related Questions