SQL Server Magazine

This example shows the author name as one column. You can add a space, enclosed in single quotes, to separate the names.

SELECT au_fname + ' ' + au_lname 
FROM authors

SELECT au_lname + ',  ' + au_fname 
FROM authors

You can also add alphanumeric strings to your output for clarity, as Screen 1 shows. In Screen 1, note the spaces after the text in 'The author ' and before and after the text ' lives in '. This approach is one you can use to format your output. Another technique is to use the SPACE() function to insert spaces:

SELECT 'The author' +
SPACE(1) + au_fname +
SPACE(1) + au_lname +
SPACE(2) + 'lives in' +
SPACE(2) + city
FROM authors
WHERE au_lname LIKE
'Green'

Character Functions
SQL Server provides many character functions, some of which you'll recognize from programming languages. For example, SUBSTRING selects a subset of the character data, starting at a specified character and continuing for as many characters as you specify. You use SUBSTRING, for example, to display the author name in the format lname, firstinitial, as in Greene, M.

SELECT au_lname + ', ' + 
   SUBSTRING (au_fname,1,1) + '.',  au_id
   FROM authors
WHERE au_lname LIKE 'Greene'

If you want to take characters from the right of the column, for example, the last four digits of a Social Security number, use the RIGHT function.

SELECT au_lname, au_fname, RIGHT (au_id, 4)
FROM authors

There is no LEFT function. Instead, use SUBSTRING starting at character 1. Expected functions such as removing leading and trailing blanks (LTRIM, RTRIM) and converting to upper- or lowercase are available. I find the STUFF function useful. This function replaces a string anywhere in the data with another string. In the Pubs database, the author IDs look suspiciously like Social Security numbers. Suppose that I want the receptionist to verify the authors' identities when they call the office, but I don't want to give the Social Security numbers to anyone but the accounting department. Screen 2 shows a query I can use to generate the author ID list for the receptionist.

This example contains a search on the author name, which is character data. SQL Server supports various ways to search for character data. You can use an equality to search, as in:

WHERE au_lname = 'Green'

or LIKE, which imposes an additional filter when you use it in Query Analyzer.

WHERE au_lname LIKE 'Green'

Prev. page     1 [2] 3     next page