|
|
The mission is to retrive address list of all
employees concatenating street and house-number fields. In
MS-Access you have to know the simple trick.
Dataset is following:
| FirstName | LastName | city | street | housenum |
| john | silver | london | ukitgam | 780/19 |
| gret | garbo | berlin | akstrass | 102 |
| le | chen | berlin | oppenhaim | NULL |
| daniel | defo | rome | corso vinchi | 25 |
| maggi | forth | london | bolken str | NULL |
| voich | lutz | paris | pinchi platz | NULL |
| anna | poperplatz | milan | via domani | 15/4 |
If you'll write following query:
SELECT Trim(a.FirstName) & ' ' & Trim(a.LastName) AS employee_name,
a.city, a.street + ' ' + a.housenum AS address
FROM Employees AS a
|
The result will be as this:
| employee_name | city | address |
| john silver | london | ukitgam 780/19 |
| gret garbo | berlin | akstrass 102 |
| le chen | berlin | NULL |
| daniel defo | rome | corso vinchi 25 |
| maggi forth | london | NULL |
| voich lutz | paris | NULL |
| anna poperplatz | milan | via domani 15/4 |
Instead the correct syntax have to be:
SELECT Trim(a.FirstName) & ' ' & Trim(a.LastName) AS employee_name,
a.city, a.street & (' ' +a.housenum) AS address
FROM Employees AS a
|
As a result query returns street name even if house-number is NULL:
| employee_name | city | address |
| john silver | london | ukitgam 780/19 |
| gret garbo | berlin | akstrass 102 |
| le chen | berlin | oppenhaim |
| daniel defo | rome | corso vinchi 25 |
| maggi forth | london | bolken str |
| voich lutz | paris | pinchi platz |
| anna poperplatz | milan | via domani 15/4 |
Same query on SQLServer 2005, using isNull function:
SELECT RTrim(a.FirstName) + ' ' + RTrim(a.LastName) AS employee_name,
a.city, a.street + RTrim(' ' + isNull(a.housenum,'')) AS address
FROM Employees AS a;
|
|