Combine Two Columns and Add into One New Column

Combine two columns and add into one new column

Generally, I agree with @kgrittn's advice. Go for it.

But to address your basic question about concat(): it is useful if you need to deal with null values - and null has neither been ruled out in your question nor in the one you refer to.

If you can rule out null values, the good old (SQL standard) concatenation operator || is still the best choice, and @luis' answer is just fine:

SELECT col_a || col_b;

If either of your columns can be null, the result would be null in that case. You could defend with COALESCE:

SELECT COALESCE(col_a, '') || COALESCE(col_b, '');

But that gets tedious quickly with more arguments. That's where concat() comes in, which never returns null, not even if all arguments are null. The manual:

NULL arguments are ignored.

SELECT concat(col_a, col_b);

The remaining corner case for both alternatives is where all input columns are null in which case we still get an empty string ''. To get null instead:

SELECT CASE
          WHEN col_a IS NULL THEN col_b
          WHEN col_b IS NULL THEN col_a
          ELSE col_a || col_b
       END;

This gets more complex with more columns quickly. Again, use concat(), but add a check for the special condition:

SELECT CASE WHEN (col_a, col_b) IS NULL THEN NULL
            ELSE concat(col_a, col_b) END;

How does this work?

(col_a, col_b) is shorthand for ROW (col_a, col_b). And a row type is only null if all columns are null. Detailed explanation:

• NOT NULL constraint over a set of columns

Also, use concat_ws() to add separators between elements (ws for "with separator").

---

An expression like the one in Kevin's answer:

SELECT $1.zipcode || ' - ' || $1.city || ', ' || $1.state;

is tedious to prepare for null values in PostgreSQL 8.3 (without concat()). One way (of many):

SELECT COALESCE(
         CASE
            WHEN $1.zipcode IS NULL THEN $1.city
            WHEN $1.city    IS NULL THEN $1.zipcode
            ELSE $1.zipcode || ' - ' || $1.city
         END, '')
       || COALESCE(', ' || $1.state, '');

Function volatility is only STABLE

concat() and concat_ws() are STABLE functions, not IMMUTABLE because they can invoke datatype output functions (like timestamptz_out) that depend on locale settings.

Explanation by Tom Lane.

This prohibits their direct use in index expressions. If you know that the result is actually immutable in your case, you can work around this with an IMMUTABLE function wrapper. Example here:

• Does PostgreSQL support "accent insensitive" collations?

MySQL combine two columns into one column

My guess is that you are using MySQL where the + operator does addition, along with silent conversion of the values to numbers. If a value does not start with a digit, then the converted value is 0.

So try this:

select concat(column1, column2)

Two ways to add a space:

select concat(column1, ' ', column2)
select concat_ws(' ', column1, column2)

Pandas - combine column values into a list in a new column

try this :

t['combined']= t.values.tolist()

t
Out[50]: 
         A         B     C        D                       combined
0    hello         1  GOOD  long.kw      [hello, 1, GOOD, long.kw]
1     1.20  chipotle   NaN    bingo    [1.2, chipotle, nan, bingo]
2  various       NaN  3000   123.46  [various, nan, 3000, 123.456]

MySQL combine two columns and add into a new column

Create the column:

ALTER TABLE yourtable ADD COLUMN combined VARCHAR(50);

Update the current values:

UPDATE yourtable SET combined = CONCAT(zipcode, ' - ', city, ', ', state);

Update all future values automatically:

CREATE TRIGGER insert_trigger
BEFORE INSERT ON yourtable
FOR EACH ROW
SET new.combined = CONCAT(new.zipcode, ' - ', new.city, ', ', new.state);

CREATE TRIGGER update_trigger
BEFORE UPDATE ON yourtable
FOR EACH ROW
SET new.combined = CONCAT(new.zipcode, ' - ', new.city, ', ', new.state);

---

Related Topics