0

I trying to concatenating the four-column to one column. i using coalease for replacing null as space but while concatenating. if the particular column has null value then i dont need spaces in concatenated results

UPDATE vt_pc_incremental_gold_contact SET "Concatenated Phone Number" = 
trim(coalesce("Contact Phone Country Code",'')||' '||trim(coalesce("Contact Phone Area Code",''))||' '||trim(coalesce("Contact Phone Number",''))
||' '||trim(coalesce("Contact Phone Extension",'')));

I need results like this

enter image description here

Improve this question
1
  • Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/… Commented Feb 27, 2020 at 7:30

3 Answers 3

Reset to default
2

You can use replace() function to replace those double spaces.

UPDATE vt_pc_incremental_gold_contact SET "Concatenated Phone Number" = 
replace(trim(coalesce("Contact Phone Country Code",'')||' '||trim(coalesce("Contact Phone Area Code",''))||' '||trim(coalesce("Contact Phone Number",''))
||' '||trim(coalesce("Contact Phone Extension",''))), '  ', ' ');
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

User "concat_ws". It will solve your problem.

UPDATE vt_pc_incremental_gold_contact SET "Concatenated Phone Number" = 
concat_ws(' ',trim("Contact Phone Country Code")
   ,trim("Contact Phone Area Code"),trim("Contact Phone Number"),trim("Contact Phone Extension"));
Improve this answer

Comments

0

Use concat_ws() this will properly deal with empty strings and spaces

UPDATE vt_pc_incremental_gold_contact 
  SET "Concatenated Phone Number" = concat_ws(' ', "Contact Phone Country Code", "Contact Phone Area Code", "Contact Phone Number", "Contact Phone Extension");
Improve this answer

Comments

Your Answer

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