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

Question

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?

where's the second table come in? if you're just concatenating two columns in a table, why not add that as another column? — serakfalcon, Jul 14 '14 at 04:06
Is there a good reason to add a new column? You can just generate the concatenated string on the fly. — shree.pat18, Jul 14 '14 at 04:08
@StampinStephie you originally had a couple typos in your question where you said "table" instead of "column." That's where that comment came from. I edited the question, though, so they're fixed now. — Matthew Haugen, Jul 14 '14 at 04:25
Please note: "generating the concatenated string on the fly" from @shree.pat18 is the preferred way, because then your data doesn't get out-of-date. If you write it once now and someone does a name change in your DB, then your FullNames will be out of date. Also, you are duplicating data - which you should never do. By concatinating the result "on the fly" you're essentially just referencing the base data, so you keep ONE-AND-ONLY-ONE copy of the master data. That way if it gets updated, you get the updates. — m1m1k, Jun 21 '19 at 16:08

Matthew Haugen · Answer 1 · 2014-07-23T04:31:05.377

20

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);

edited Jul 23 '14 at 04:31

answered Jul 14 '14 at 04:06

Matthew Haugen

12,916
5
38
54

ok i did that and it looked like it added it but when i did a select to display it, i got invalid number Error starting at line : 2 in command - SELECT full_name FROM EMPS Error report - SQL Error: ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: *Action: – Stampin Stephie Jul 14 '14 at 04:23
@StampinStephie Interesting. I don't even recognize the syntax of that error... What version of SQL Server are you using? – Matthew Haugen Jul 14 '14 at 04:25
its SQL developer 4.0.2.15 – Stampin Stephie Jul 14 '14 at 04:26
That sure sounds like an Oracle Server, not Microsoft SQL Server as you tagged. I'm not familiar with the syntax for Oracle SQL. What happens when you do a select with a similar column? `SELECT FirstName + ' ' + LastName FROM EMPS` – Matthew Haugen Jul 14 '14 at 04:30
i get the proper output with out the "full name". the column is named FIRST_NAME||''||LAST_NAME – Stampin Stephie Jul 14 '14 at 04:34
sorry i tagged thw wrong thing, new here. its Oracle SQL developer – Stampin Stephie Jul 14 '14 at 04:35
@StampinStephie No problem. I fixed the tag for you as well. I see you marked Jacky's answer as accepted, did that work for you, then? – Matthew Haugen Jul 14 '14 at 04:38
no sorry, it didn't. I unmarked it. thanks for all your help. – Stampin Stephie Jul 14 '14 at 04:40
So wait, you haven't found your solution? Just to be clear? If not, I'd try the same syntax as I posed in my answer, but using the double-pipe operator rather than plus signs. It seems that's what Oracle SQL uses as a concatenation operator. – Matthew Haugen Jul 14 '14 at 04:45
I tried that and it doesn't work. no solution yet. I can do this SELECT emps.first_name || ' ' || emps.last_name AS FULL_NAME FROM emps; and get the correct column output but its not creating a new column. when i try to do this SELECT employee_id,FULL_NAME,salary,department_id FROM EMPS ORDER BY last_name; it shows up as a column but no data. and I have also done an ALTER TABLE to add it as a column. – Stampin Stephie Jul 14 '14 at 04:49
@StampinStephie I'll look a bit into the documentation. I haven't found a single reference to calculated columns in Oracle yet, though. Which is kind of strange. – Matthew Haugen Jul 14 '14 at 04:56
Out of curiosity, is there a reason you're looking to do this as a computed column as compared to explicitly in your `SELECT`s? – Matthew Haugen Jul 14 '14 at 05:21
Aha! (I think). According to [this](http://www.oracle.com/technetwork/database/rdb/automatic-columns-132042.pdf), the syntax is `...FullName COMPUTED BY first_name||' '||last_name`. – Matthew Haugen Jul 14 '14 at 05:23
Hi Matthew. I got a solution for the virtual column, I needed to do this: ALTER TABLE EMPS MODIFY ( FULL_NAME VARCHAR2(50) GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL ); thanks again for all your help! – Stampin Stephie Jul 14 '14 at 23:38
it's not working properly, i used `ALTER TABLE cars ADD FullName varchar(500) AS (concat(\`FirstName\`,'-',\`LastName\`));` – TechDogLover OR kiaNasirzadeh Jun 05 '23 at 16:43

score 6 · Answer 2 · answered Nov 07 '16 at 11:29

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

score 2 · Answer 3 · edited Jul 14 '14 at 04:32

2

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.

edited Jul 14 '14 at 04:32

Matthew Haugen

12,916
5
38
54

answered Jul 14 '14 at 04:28

Jacky

2,924
3
22
34

score 0 · Answer 4 · edited Apr 11 '19 at 13:26

0

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

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

edited Apr 11 '19 at 13:26

Stephan Bauer

9,120
5
36
58

answered Apr 11 '19 at 13:01

Mohammed Ali

1

score 0 · Answer 5 · edited Dec 10 '22 at 09:00

0

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

edited Dec 10 '22 at 09:00

Jacky

2,924
3
22
34

answered Dec 09 '22 at 15:02

talkingtoducks

57
9

1

you will need to concatenate `' '` too, else it will return `JohnSmith` without a space. e.g `SELECT CONCAT(FirstName, ' ', LastName) as FullName From Table_name` – Jacky Dec 10 '22 at 09:00

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

5 Answers5

Linked