PRB: CONVERTing Character to an Integer Causes Syntax Error

ID: Q47180

The information in this article applies to:

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

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.

Additional query words: Transact-SQL Windows NT

Keywords : kbother SSrvGen SSrvServer SSrvWinNT
Version : winnt:4.2x,6.0,6.5,7.0
Platform : winnt
Issue type :