I have two columns in a table , say "colA", "colB" and "colC" need to be computed.

Case 1: Here, colC is a combination of colA and colB with delimiter as space.

For example , if colA = 'abc' and colB = 'xyz' then colC = 'abc xyz'.

Case 2: Here , if either of the colums (colA , colB) is null then colC should contain the non-null value among colA and colB.

For example , if colA = null and colB = 'xyz' then colC = 'xyz'. if colA = 'abc' and colB = null then colC = 'abc'.

How can I achieve this in a select statement using CASE or some other best approach?

  colA varchar(32),
  colB varchar(32)

INSERT #three(colA, colB) VALUES
  ('abc', 'xyz'),
  ('def', NULL),
  (NULL,  'ghi'),
  (NULL,  NULL);

  ColC = LTRIM(COALESCE(colA, '') + COALESCE(' '+colB,''))
FROM #three;

DROP TABLE #three;


ColA  ColB  ColC
----  ----  -------
abc   xyz   abc xyz
def   NULL  def
NULL  ghi   ghi
NULL  NULL      
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.