PRB: CONVERTing Character to an Integer Causes Syntax Error (47180)

The information in this article applies to:

Microsoft SQL Server 4.2x
Microsoft SQL Server 6.0
Microsoft SQL Server 6.5
Microsoft SQL Server 7.0

This article was previously published under Q47180

SYMPTOMS

According to the "Microsoft SQL Server Language Reference" and Books Online, you can convert from a character to an integer. However, the following statement,

   select convert(int,'A')

causes an error similar to the following:

Msg 249, Level 16, State 1
Syntax error converting CHAR value 'A' to an INT4 field

CAUSE

A convert() from a char to an integer can only be done if it "makes sense." For example, you can convert the character "1" (one) to an integer, but you cannot convert the letter "A". For example

   select convert(int,'1')

returns 1 as expected.

WORKAROUND

To return the ASCII numerical representation of a letter, use the ascii() function. For example

   select ascii('A')

returns 65.

Modification Type:	Minor	Last Reviewed:	3/14/2005
Keywords:	kbother KB47180