Presents your 
SQL SERVER E-NEWSLETTER for May 27, 2003

<------------------------------------------->

EXPLORE USES FOR THE CHAR STRING FUNCTION

CHAR is a string function that converts an int ASCII code into a
character. CHAR can also be used to insert control characters such as tabs, line
feeds, and carriage returns into character strings.

Here's the syntax for CHAR:

CHAR(integer_expression)

Integer_expression can be an integer from 0 to 255. If the integer
expression is outside the 255 range, a null will be returned.

In the following example, we'll use CHAR to print the ASCII value and
character for each character in the string Builder.com. Once the variables
are declared and initialized, the SELECT statement will go through the
string and convert each letter to its ASCII equivalent.

Note: If you want to play with this code and change the string, be sure
to change the @string char (11) argument to match the length of your word
or phrase. Otherwise, the results will only return the number of spaces
listed in the @string char argument.

First, create variables for the character string and for the current
position in the string.

DECLARE @position int, @string char(11)

Then initialize the current position and the string variables.

SET @position = 1
SET @string = 'Builder.com'
WHILE @position <= DATALENGTH(@string)
             BEGIN
                        SELECT ASCII(SUBSTRING(@string, @position, 1)),
                        CHAR(ASCII(SUBSTRING(@string, @position, 1)))
                        SET @position = @position + 1
             END
GO

--------------
66          B

--------------
117          u

--------------
105          i

---------------
108          l

--------------
100          d

---------------
101          e

---------------
114          r

---------------
46          .

--------------
99          c

--------------
111          o

--------------
109          m

Perhaps you frequently use CHAR with control characters. The most common
control characters are the tab, CHAR(9), the line feed, CHAR(10), and
the carriage return, CHAR(13).

In our next example, we'll return the mailing address from the Employees
table of the Northwind database for the employee with ID number 1 
(name, address, city, state, and zip code are on separate lines). Notice that
the CHAR(13) is right where we want the carriage return. (Hint: If your
results are showing up in just another table, you have to change the
Execute Mode drop-down menu to Results In Text, not Results In Grid.)

USE Northwind
SELECT FirstName + ' ' + LastName, + CHAR(13) + Address,
+ CHAR(13) + City, + Region + '  ' +PostalCode
FROM Employees
WHERE EmployeeID = 1

------------------
Nancy Davolio                   
507 - 20th Ave. E.
Apt. 2A                                  
Seattle         WA 98122

(1 row(s) affected)

----------------------------------------