CODEWITHSUNDEEP
sqlpostgresql
user3814846
user3814846

Reputation:

PostgreSQL:string concatanation in select query

Given below is my select query

select 
     case
      when mobile||', '||phoneoff  <>', ' then mobile||', '||phoneoff 
      when phoneoff='' and mobile<>''  then mobile 
      when phoneoff<>'' and mobile='' then phoneoff 
     else 'NIL' 
     end as "Concat_result",phoneoff,mobile
from gtab12


Result 

      Concat_result|  phoneoff  | mobile
-------------------+------------+----------  
9544070335, 2812111|2812111     |9544070335
                NIL|            |
     , 0479-2436762|0479-2436762|
       9495758057, |            |9495758057


what I'm trying to achieve is,
if both phoneoff and mobile have value then it should concatenate with a , see Concat_result col in the first row in the result
If phoneoff and mobile are = '' then Concat_result should print NIL
If phoneoff<>'' and mobile='' then Concat_result should print phoneoff only (see row 3 in my result its showing phoneoff along with ,) and same for mobile<>''


Expecting Result

      Concat_result|  phoneoff  | mobile
-------------------+------------+----------  
9544070335, 2812111|2812111     |9544070335
                NIL|            |
      0479-2436762 |0479-2436762|           
       9495758057  |            |9495758057

Upvotes: 1

Views: 60

Answers (3)

pozs
pozs

Reputation: 36274

A simple one-liner, which uses NULLIF() & COALESCE() (therefore can handle NULL values too, not just empty strings):

select coalesce(nullif(concat_ws(', ', nullif(phoneoff, ''), nullif(mobile, '')), ''), 'NIL') "Concat_result", phoneoff, mobile
from gtab12

Upvotes: 2

Andrzej Reduta
Andrzej Reduta

Reputation: 767

select 
 case
    when (mobile<>'' and phoneoff<>'') then mobile||', '||phoneoff 
    when phoneoff='' and mobile<>''  then mobile 
    when phoneoff<>'' and mobile='' then phoneoff 
    else 'NIL' 
 end as "Concat_result",phoneoff,mobile
from gtab12

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271023

Postgres has concat_ws, which can help:

select concat_ws(', ', phoneoff, mobile) as Concat_result, phoneoff, mobile
from gtab12 ;

This doesn't quite do what you want, because you care about special values and 'NIL'. So, let's put that logic in:

select (case when phoneoff = '' and mobile = '' then 'NIL'
             else concat_ws(', ',
                            (case when phoneoff <> '' then phoneoff end),
                            (case when mobile <> '' then mobile end)
                           )
        end) as Concat_result, phoneoff, mobile
from gtab12 ;

Upvotes: 2

Related Questions