Single Character Functions
Transact-SQL (T-SQL) provides four standard functions that are used to process single characters. These are used to convert between characters and their ASCII or Unicode values. ASCII is the American Standard Code for Information Interchange, an encoding standard primarily for English characters that applies a value between zero and two hundred and fifty-five to each character. Unicode is a much larger character-encoding scheme that allows a single number to identify each character in any language.
Ascii
The first of the four functions that we will consider in this article is the Ascii function. This obtains the ASCII code for a single character, returning the result as an integer. The function can be used with either Char or VarChar data. When a VarChar is provided, the ASCII code for the first character in the string is returned.
We can demonstrate by printing the result of the Ascii function in a query tool.
PRINT ascii('A') -- 65
PRINT ascii('a') -- 97
PRINT ascii('ABC') -- 65
Char
The Char function provides the reverse of Ascii. It takes an ASCII code as an integer value between zero and two hundred and fifty-five as its only argument. The return value is a Char containing the character represented by the ASCII code. If the value is not within the valid range, the function returns null.
PRINT char(65) -- A
PRINT char(97) -- a
The ASCII codes below thirty-two are for non-printing characters such as carriage return (13), line feed (10) and tab (9). These characters can be included in strings by concatenating literal text with the appropriate codes. For example, the following script prints a string containing a carriage return and line feed.
PRINT 'Hello' + char(13) + char(10) + 'World'
/* OUTPUT
Hello
World
*/
Unicode
The Unicode function provides similar functionality as the Ascii function. However, rather than converting a character to its ASCII code, it returns the Unicode value for a character. The provided string must be an NChar or NVarChar type. Using a Char or VarChar will involve implicit conversion and can cause invalid results for higher-numbered characters.
The following examples demonstrate the use of the function. Note the use of the "N" prefix to indicate that the literal strings should be treated as Unicode.
PRINT unicode(N'A') -- 65
PRINT unicode(N'a') -- 97
PRINT unicode(N'Ā') -- 256
PRINT unicode(N'ā') -- 257
NChar
The last of the four functions described in this article is NChar. As with Char, this function returns the character represented by a code. In this case, the code provided is a Unicode value with a range of zero to 65535. Values outside of this range cause a null result.
PRINT nchar(65) -- A
PRINT nchar(97) -- a
PRINT nchar(256) -- Ā
PRINT nchar(257) -- ā
24 November 2009