Convert letter to integer based on order in alphabet

Is there any way I can convert a letter of the alphabet to its numerical
position in the alphabet.
So if I select a field with 'A' I want to return 1, 'B' = 2, ... 'Z' = 26
I'd prefer not to join to a seperate reference table if possible.Terri wrote:
> Is there any way I can convert a letter of the alphabet to its numerical
> position in the alphabet.
> So if I select a field with 'A' I want to return 1, 'B' = 2, ... 'Z' = 26
> I'd prefer not to join to a seperate reference table if possible.
How about this?
SELECT ASCII(YourOneCharColumn) - ASCII('A') + 1 FROM YourTable|||CREATE TABLE alpha (alpha_char CHAR(1) NOT NULL PRIMARY KEY, alpha_num
INTEGER NOT NULL) ;
SELECT alpha_num
FROM some_table AS T
JOIN alpha AS A
ON T.col = A.alpha_char ;
David Portas
SQL Server MVP
--
"Terri" <terri@.cybernets.com> wrote in message
news:di6qca$357$1@.reader2.nmix.net...
> Is there any way I can convert a letter of the alphabet to its numerical
> position in the alphabet.
> So if I select a field with 'A' I want to return 1, 'B' = 2, ... 'Z' = 26
> I'd prefer not to join to a seperate reference table if possible.
>