convert char to int

22 Jul 2005 7:13 PM

vickie k. hoffmann

Hi all, Is there a function in SQL that will convert letters to numbers
corresponding to their placement in the alphabet?

For Example:
a = 1
b = 2
c = 3
d = 4
... ..
y = 25
z = 26

Thanks!

vickie

22 Jul 2005 7:24 PM

Anith Sen

See the ASCII function in SQL Server Books Online. Developing a logic based
on it shouldn't be hard.

--
Anith

22 Jul 2005 7:24 PM

Rick Sawtell

Show quote

"vickie k. hoffmann" <vickiehoffm***@orbisinc.net> wrote in message
news:O2HzmFvjFHA.1412@TK2MSFTNGP09.phx.gbl...
> Hi all, Is there a function in SQL that will convert letters to numbers
> corresponding to their placement in the alphabet?
>
> For Example:
> a = 1
> b = 2
> c = 3
> d = 4
> .. ..
> y = 25
> z = 26
>
> Thanks!
>
> vickie
>
>

Not that I know of, but you could write one. Or simply use a join, or take
advantage of the ASCII function.

Something like this. Covert the letter to lower case, then get the ASCII
value and subtract 96 from it. (Lower case A starts at 96.

SELECT ASCII(LOWER('A')) - 96

Rick Sawtell

22 Jul 2005 8:00 PM

Sean Nolan

And uppercase A is 65, so this will do it even more simply :-)

SELECT ASCII('A') - 64

And on SQL Server 2000 you could create your own user-defined function:
CREATE FUNCTION LetterNumber (@Letter char)
RETURNS int
AS
BEGIN
RETURN ASCII(UPPER(@Letter)) - 64
END

Sean

Show quote

"Rick Sawtell" <r_sawt***@hotmail.com> wrote in message
news:u3CJfLvjFHA.2472@TK2MSFTNGP15.phx.gbl...
>
> "vickie k. hoffmann" <vickiehoffm***@orbisinc.net> wrote in message
> news:O2HzmFvjFHA.1412@TK2MSFTNGP09.phx.gbl...
>> Hi all, Is there a function in SQL that will convert letters to numbers
>> corresponding to their placement in the alphabet?
>>
>> For Example:
>> a = 1
>> b = 2
>> c = 3
>> d = 4
>> .. ..
>> y = 25
>> z = 26
>>
>> Thanks!
>>
>> vickie
>>
>>
>
> Not that I know of, but you could write one. Or simply use a join, or
> take
> advantage of the ASCII function.
>
> Something like this. Covert the letter to lower case, then get the ASCII
> value and subtract 96 from it. (Lower case A starts at 96.
>
> SELECT ASCII(LOWER('A')) - 96
>
>
> Rick Sawtell
>
>
>
>
>
>
>
>