Delimiting and Concatenating

The examples in this chapter are simplified in that they don't contain variables or literal values that require delimiter characters. Concatenating literal values and variables is common practice, and unless you know the rules, a statement can produce errors. Systems aren't uniform in this regard, and we recommend you refer to your system's documentation to learn the specific characters and rules for concatenating. SQL Server uses the plus sign (+) as its concatenation character, and our examples reflect this requirement. Table 18.4 lists the concatenation characters for the systems we use throughout this book.

Table 18.4: CONCATENATION CHARACTERS

Character	RDBMS	Notes
+	SQL Server
&	Access
||	Oracle
||	MySQL	Only if MySQL is launched with the --ansi option. Use the native CONCAT() function instead.

A simple example of concatenation is displaying the first and last names from the Employees table, in the same column. To do so, use the simple statement:

SELECT FirstName + ' ' + LastName AS Employee

SQL displays both names in the same column as shown in Figure 18.35.

Figure 18.35: Use your system's concatenation character to combine data.

General concatenation rules stipulate that you should delimit literal values; each system is unique in its requirements, so check your documentation. In this context, value can be a string, number, or date. Table 18.5 lists the delimiters and their purpose for the systems used throughout this book. For the most part, you won't delimit values.

Table 18.5: DELIMITERS

character	Name	System	Purpose	Example
'	Single quotation mark	SQL Server and Access	Delimits literal strings and dates	"string"
"	Double quotation mark	Access	Delimits literal strings	'string'
#	Pound sign	Access	Delimits dates	#datestring#

Tip 

Access accepts single or double quotation marks as a string delimiter. However, we recommend you get in the habit of using the single quotation mark since that's the character SQL Server uses. Should upsizing be in your future, replacing string delimiters is one headache you can avoid.

SQL statements are treated as strings in a script, and you must delimit them accordingly. For instance, you might send a request to retrieve all the employee records using the ADO Execute method in the form:

Set rst = Conn.Execute("SELECT * FROM Employees")

The request returns an error message if you omit the double quotation delimiters.

When passing a literal string as criteria, delimit the string using the single quotation mark character in the form

strSQL = "SELECT * FROM Employees WHERE LastName = 'Harkins'")  Set rst = Conn.Execute(strSQL)

If you omit the single quotation marks, SQL assumes Harkins is a variable and returns an error message (unless, of course, there really is a variable named Harkins, which creates an entirely different problem).

Don't forget that some systems also delimit date values. For instance, SQL Server uses the single quotation mark with dates and literal strings; Access uses the pound character to delimit dates. Both of the following statements are correctly delimited, depending on the system:

strSQL = "SELECT * FROM Orders WHERE ShippedDate < '8-1-1977'"  strSQL = "SELECT * FROM Orders WHERE ShippedDate < #8-1-1977#"

The first statement works in SQL Server, but returns an error message in Access. The second statement works in Access but returns an error message in SQL Server. Knowing what your system expects is vital to the success of your script.

Passing a variable requires a bit of concatenation, and delimiters can also be required, depending on the requirements of your system. For instance, the following statements request all the records in which the UnitPrice equals the value stored in a variable named intUnitPrice:

strSQL = "SELECT * FROM Products WHERE UnitPrice = " + intUnitPrice  strSQL = "SELECT * FROM Products WHERE UnitPrice = " & intUnitPrice 

If the variable stores a string or (in some systems) a date, you need to delimit that string using the syntax:

strSQL = "SELECT * FROM Employees WHERE LastName = '"& strLastName & "'"

Remember to use the appropriate delimiter for the value's datatype and to meet system requirements.