Convert a letter into a number

Question

I am building the back end of a web application which is processing a significant portion of data and the front end developers are looking for a stable integer code to use in joining data.

The current integer values they are trying to use are surrogate keys which will change going forward leading to a number of problems. Each table has a alphanumeric code and I am looking for a way in which I could convert this into a stable int.

EG convert a code 'AAAA' into 1111 or MMMM into 13131313

Could anyone tell me if this is at all possible.

Thanks,

Solution 1

McNets' comment seems to be a very good approach...

If you can be sure, that you have

• plain ASCII characters
• Not more than 4 letters

You might cast the string to VARBINARY(4) and cast this to INT:

DECLARE @dummy TABLE(StrangeCode VARCHAR(10));
INSERT INTO @dummy VALUES
 ('AAAA'),('MMMM'),('ACAC'),('CDEF'),('ABCD');

SELECT CAST(CAST(StrangeCode AS VARBINARY(4)) AS INT)
FROM @dummy;

The result

1094795585
1296911693
1094926659
1128547654
1094861636

If you need bigger number, you might go up to BIGINT

Solution 2

A way is using CTE like this:

;with tt(i, c1, c2) as (
    select 1, c, replace(c,char(65), 1)
    from yourTable
    union all
    select i+1, c1, c2= replace(c2,char(65+i), i+1)
    from tt
    where i < 26
)
select c1, cast(c2 as bigint) num
from tt
where i = 26;