22 Jun
Combining One or More Table Fields Into One
I was asked to populate two new fields “FullName” and “LastNameFirstName” where “FullName” is the First Name followed by Last Name with a space in between and “LastNameFirstName” is the Last Name followed by the First Name with a comma in between.
The following is a sample table “STUDENT”:
| FIRSTNAME | LASTNAME |
|---|---|
| Sharon | Smith |
| Teerawul | Chaisanit |
The office was using MS SQL 7.0 database.
I used MS Query Analyzer and run this simple script:
Update STUDENT
set FullName = {fn CONCAT({fn RTRIM(firstname)},
{fn CONCAT(' ',{fn RTRIM(lastname)})})},
LastNameFirstName = {fn CONCAT({fn CONCAT({fn RTRIM(Lastname)},', ')},
{fn RTRIM(Firstname)})}
where graduationyear = '2007'
Here is the result of the “STUDENT” table after update:
| FIRSTNAME | LASTNAME | FULLNAME | LASTNAMEFIRSTNAME |
|---|---|---|---|
| Sharon | Smith | Sharon Smith | Smith, Sharon |
| Teerawul | Chaisanit | Teerawul Chaisanit | Chaisanit, Teerawul |
Posted
on
Friday, June 22nd, 2007 at 8:06 pm under
