Add a Column That Represents a Concatenation of Two Other Varchar Columns

How do I add a concatenated column to existing table?

What is wrong is that you want an update after adding the column:

alter table "TableName" add column "NameColumn" varchar(255);  -- or whatever

update "TableName
    set "NameColumn" = concat(ColumnA, ColumnB, ColumnC);

Postgres doesn't directly support computed columns. There are various more cumbersome workarounds, some suggested here by Erwin Brandstetter.

How do I create a column that is a concatenation of two other columns in mariadb?

Simply:

ALTER TABLE employees 
ADD COLUMN Employee VARCHAR(100) AS (CONCAT(first_name, ' ' , last_name));

how to concat two columns into one with the existing column name in mysql?

As aziz-shaikh has pointed out, there is no way to suppress an individual column from the * directive, however you might be able to use the following hack:

SELECT CONCAT(c.FIRSTNAME, ',', c.LASTNAME) AS FIRSTNAME,
       c.*
FROM   `customer` c;

Doing this will cause the second occurrence of the FIRSTNAME column to adopt the alias FIRSTNAME_1 so you should be able to safely address your customised FIRSTNAME column. You need to alias the table because * in any position other than at the start will fail if not aliased.

Create a table with a column that has 2 concatenated values from different columns?

Postgres now supported generated columns, so you can add such a column as:

alter table users add full_name varchar(255) 
    generated always as (name || ' ' || last_name);

Note that I did not add this as the id but as a new column. The id should be a number so it can be used efficiently for indexes and foreign key references.

In addition, you should never put PII into primary keys. That just proliferates privacy issues throughout the entire database.

SQL- How to concatenate two columns with varchar types?

You don't have to use the + operator. Try this:

SELECT 
    f.name, 
    CONCAT( m.firstname, " ", m.surname ) AS mem_name
FROM  `Bookings` b
JOIN  `Members` m ON m.memid = b.memid
JOIN  `Facilities` f ON b.facid = f.facid
WHERE f.name LIKE  '%Tennis%'
LIMIT 0, 30

How to concatenate multiple VARCHAR columns into single CLOB or LONG column in Oracle

You can use select id, to_clob(comment1) || comment2 from table group by id. But generally better way is modify the database design and add clob column into the table.

Concatenate columns of multiple columns and multiple rows into one varchar value, when no of columns is dynamic

Here is a dynamic way

Slightly different approach from your static query. To make it dynamic, Use while loop or CURSOR to generate the Task1 + Task2 + ..TaskN. Then use it in Select query.

DECLARE @columns VARCHAR(50)='Task1,Task2,Task3', -- Pass the list of column names 
        @int     INT = 1,
        @sql     VARCHAR(8000)

SET @sql = '    ;WITH cte
         AS (SELECT *,
                    ''Cycle-'' + Cast(CycleNum AS VARCHAR(10)) + ''::'' '

WHILE @int <= Len(@columns) - Len(Replace(@columns, ',', '')) -- To find the number of Tasks in list
  BEGIN
      SET @sql += + '+''Task' + Cast(@int AS VARCHAR(10))
                  + '~''+' + 'Cast(Task'
                  + Cast(@int AS VARCHAR(10))
                  + ' AS VARCHAR(50)) + '','''
      SET @int += 1
  END

SET @sql += '   AS concat_dates
             FROM   #tempTable)
    SELECT DISTINCT FacilityName,
                    LEFT(CycleData, Len(CycleData) - 1)
    FROM   cte a
           CROSS apply(SELECT b.concat_dates + '',''
                       FROM   cte b
                       WHERE  a.FacilityName = b.FacilityName
                       FOR xml path('''')) cs (CycleData) 

                       '
--print @sql -- uncomment it to debug if you have any error when executing dynamic code
EXEC (@sql) 

Not to worry about the usage of While Loop/CURSOR since we are not doing any resource intensive operations inside the loop.

Static Query will looking like this

;WITH cte
     AS (SELECT *,
                'Cycle-' + Cast(CycleNum AS VARCHAR(10))
                + '::' + 'Task1~' + Cast(Task1 AS VARCHAR(50))
                + ',' + 'Task2~' + Cast(Task2 AS VARCHAR(50))
                + ',' AS concat_dates
         FROM   #tempTable)
SELECT DISTINCT FacilityName,
                LEFT(CycleData, Len(CycleData) - 1)
FROM   cte a
       CROSS apply(SELECT b.concat_dates + ','
                   FROM   cte b
                   WHERE  a.FacilityName = b.FacilityName
                   FOR xml path('')) cs (CycleData) 

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)

---

Related Topics