BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Single Character Functions

The forty-eighth part of the SQL Server Programming Fundamentals tutorial looks at single character string processing functions. This article describes four functions that allow the conversion between characters and ASCII or Unicode values.

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