CODEWITHSUNDEEP
sqloracle-databaseconcatenationoracle-sqldeveloper
Stampin Stephie
Stampin Stephie

Reputation: 269

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

I have an employees table and I want to add a third column valued as the concatenation of the first and last name called "FullName". How can I accomplish that without losing any data from either of the first two columns?

Upvotes: 26

Views: 90901

Answers (5)

talkingtoducks
talkingtoducks

Reputation: 57

In addition to @Jacky 's answer, if you are trying to add this to a query and not the table, there is also the CONCAT() function that you can use in the select statement

SELECT CONCAT(FirstName, ' ', LastName) as FullName
FROM table_name

Upvotes: 0

Mohammed Ali
Mohammed Ali

Reputation: 1

(For single result use equal to in the where condition)

select * 
from TABLE_name 
where (Column1+Column2) in (11361+280,11365+250)

Upvotes: 0

mansi
mansi

Reputation: 877

If you need fullname column all time when you select from database then you can create computed column at the time of creation of your table employee.

for example:

CREATE TABLE Employee
(
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  FullName AS CONCAT(FirstName,' ',LastName)
)

INSERT INTO Employee VALUES ('Rocky','Jeo')

SELECT * FROM Employee 

  Output:

  FirstName  LastName  FullName
  Rocky      Jeo       Rocky Jeo

Upvotes: 6

Matthew Haugen
Matthew Haugen

Reputation: 13286

Quick preface: this answer was based on the originally incorrect tag that this question was relating to SQL Server. I'm no longer aware of its validity on Oracle SQL Developer.

ALTER TABLE Employees ADD FullName AS (FirstName + ' ' + LastName)

Although in practice I'd advise that you do that operation in your SELECT. That's somewhat personal preference, but I tend to think doing things in your end queries is a bit cleaner, more readable, and easier to maintain than storing extra, calculated columns.

Edit:

This was eventually found as the answer, and listed by the OP as a comment on this post. The following is appropriate syntax for Oracle Sql Database.

ALTER TABLE emps MODIFY (FULL_NAME VARCHAR2(50) GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL);

Upvotes: 21

Jacky
Jacky

Reputation: 3259

It depends on your purpose, whether you really need to add a new column to your database, or you just need to query out the "full name" on an as-needed basis.

To view it on the fly, just run the query

SELECT firstname + ' ' + lastname AS FullName FROM employees

Beyond that, you also can create a simple Stored Procedure to store it.

Upvotes: 2

Related Questions