Saturday, March 17, 2012


There are times when you would like to combine two fields into one. A common example is names. In your database you probably have the first and last names stored in separate fields as shown below from our bookstore database. But what if you want to show the names as just one full name?

To combine the first name and last name into one field use the concatenation operator. In standard SQL this is the || (two of the pipe symbol at the far right of the keyboard). This works in Oracle. In SQL Server, shown in our example below use the + sign. The concatenation operator will create a new calculated field from the results of the operation. You will probably want to give this calculated field its own name. In the example below I called it customer_mail_name.

select field1 + field2 as newname, other columns
from yourtable;

SQL Script and results
The script below performs the concatenation. Note the results are still sorted by the last name even though the mail name starts with the first name. We also put a space between the names so they don't run together.

No comments:

Post a Comment