CODEWITHSUNDEEP
ruby-on-railspostgresql
bkunzi01
bkunzi01

Reputation: 4561

Change value of column from select statement

I need to change the value returned by a select statement. Currently, when I select a certain column the value returned is either '1', '2', or NULL. I'd like to return a different value in place of '1' and '2' such as "18". I've tried using the translate function to no avail. How is this done with postgresql?

Currently was trying:

.select("concat_ws(', ', translate(e110618, '12', '18'), e110717, e110519)")

When I remove the translate it works but the output (1,2,1) isn't what I need.

Upvotes: 0

Views: 218

Answers (1)

GMB
GMB

Reputation: 222482

I don't think that translate() is really what you need. It does a character-based search/replace, whereas you seem to be looking to match the whole string.

You can use a case expression instead. This is standard SQL that works in most RDBMS.

case when e110618 in ('1', '2') then 18 else e110618 end

If the only other possible value than 1 and 2 is null, then this can simplified, since case returns null by default:

case when e110618 in ('1', '2') then 18 end

Note: if e110618 is a numeric datatype (and not a string), then you should remove the single quotes around 1 and 2.

In your code:

select(
    "concat_ws(', ', case when e110618 in ('1', '2') then 18 end, e110717, e110519)"
)

Upvotes: 1

Related Questions