|
|
Jet SQL have certain amount of string manipulation functions.
Mid() and InStr()
are among the most popular. In the following example we need to
preformate address fields from the table in order to use them in mailing list:
SELECT 'To Mr./Ms ' & Mid(CustomerName,InSTR(CustomerName,' ')+1) AS
MailName, 'Mail-To: ' & City & ', ' & Street & ' '
& Trim(STR(housenum))AS MailAddress
FROM Customers
WHERE City = 'keflavik';
|
Expression InSTR(CustomerName, ' ') finds
position of the first blank space in the customer name. The next expression
Mid(CustomerName,InSTR(CustomerName,' ')+1)
retrieves only family name that supposed to came after the space.
The query returns results like these:
| MailName | MailAddress |
| To Mr./Ms Simpson | Mail-To: Keflavik, suho 2 |
| To Mr./Ms Nass | Mail-To: Keflavik, suho 2 |
| To Mr./Ms Depp | Mail-To: Keflavik, suho 2 |
|