How to Receive Data from a Single Table - How to code string expressions

(Page 5 of 6 )


 
A string expression consists of a combination of one or more character columns and literal values. To combine, or concatenate, the columns and values, you use the concatenation operator (+). This is illustrated by the examples in figure 3-5.

The first example shows how to concatenate the VendorCity and VendorState columns in the Vendors table. Notice that because no alias is assigned to this column, it doesn’t have a name in the result set. Also notice that the data in the VendorState column appears immediately after the data in the VendorCity column in the results. That’s because of the way VendorCity is defined in the database. Because it’s defined as a variable-length column (the varchar data type), only the actual data in the column is included in the result. In contrast, if the column had been defined with a fixed length, any spaces following the name would have been included in the result. You’ll learn about data types and how they affect the data in your result set in chapter 8.

The second example shows how to format a string expression by adding spaces and punctuation. Here, the VendorCity column is concatenated with a string literal, or string constant, that contains a comma and a space. Then, the VendorState column is concatenated with that result, followed by a string literal that contains a single space and the VendorZipCode column.

Occasionally, you may need to include a single quotation mark or an apostrophe within a literal string. If you simply type a single quote, however, the system will misinterpret it as the end of the literal string. As a result, you must code two quotation marks in a row. This is illustrated by the third example in this figure.

How to concatenate string data

Figure 3-5.   How to code string expressions

  SELECT VendorCity, VendorState, VendorCity + VendorState
  FROM Vendors

How to format string data using literal values

  SELECT VendorName,
      VendorCity + ', ' + VendorState + ' ' + VendorZipCode AS Address
  FROM Vendors

How to include apostrophes in literal values

  SELECT VendorName + '''s Address: ',
      VendorCity + ', ' + VendorState + ' ' + VendorZipCode
  FROM Vendors

Description

• A string expression can consist of one or more character columns, one or more literal values, or a combination of character columns and literal values.
  
• The columns specified in a string expression must contain string data (that means they’re defined with the char or varchar data type).
  
• The literal values in a string expression also contain string data, so they can be called string literals or string constants. To create a literal value, enclose one or more characters within single quotation marks ( ’ ).
  
• You can use the concatenation operator (+) to combine columns and literals in a string expression.
  
• You can include a single quote within a literal value by coding two single quotation marks as shown in the third example above.

Next: How to code arithmetic expressions >>
 

More MS SQL Server Articles
More By Murach Publishing

Comments On: How to Receive Data from a Single Table

· This article is an excerpt from the book "Murach's SQL for SQL Server," published by...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter three of the book Murach's SQL for SQL Server, written by Bryan Sylverson (Murach; ISBN: 1890774162). Check it out today at your favorite bookstore. Buy this book now.