SQL simple -> Format mailing address using Mid and InStr functions

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:
MailNameMailAddress
To Mr./Ms SimpsonMail-To: Keflavik, suho 2
To Mr./Ms NassMail-To: Keflavik, suho 2
To Mr./Ms DeppMail-To: Keflavik, suho 2



sqlexamples.info