SQL Server Computed Column - primary key to alpha characters

Question

I need to create, and then store in the db table, an alpha representation of a primary key.

I found a wonderful PHP script that does exactly what I want. However, I am not sure of the best way to implement it directly in SQL Server.

I believe that I should be using a persistent Computed Column..? However, I am not sure how to convert the PHP function into one that work in SQL Server. Any assistance/education would be greatly appreciated.

The PHP code:

function alphaID($n)
{
    for ($r = ""; $n >= 0; $n = intval($n / 26) - 1) {
        $r = chr($n%26 + 0x41) . $r;
    }
    return str_pad($r, 6, 'A', STR_PAD_LEFT);
}

echo alphaID(0) . "<br>"; // returns "AAAAAA"
echo alphaID(1) . "<br>"; // returns "AAAAAB"

Hi and welcome to SO. I am not even going to try to decipher that PHP because I have never written a line of it. Can you explain what that does? — Sean Lange, Feb 25 '19 at 15:55
Why not just store the key? Even if there's a reason the actual key can't be stored why convert that in *SQL*? You can't apply that function to fields in queries if you want to use indexes on them which means all primary, foreign key values will have to be converted *before* they get stored in the database. The key itself will end up being quite a bit bigger than the actual ID too. A single letter can represent only 26 out of the 256 possible values a char can hold — Panagiotis Kanavos, Feb 25 '19 at 16:04
@SeanLange The PHP script will take a number and convert it to a unique alpha-only string. For example: "0" returns "AAAAAA" "1" returns "AAAAAB" — mvanolden, Feb 25 '19 at 16:37
@PanagiotisKanavos The alpha-id is required by the client. They need a '6-digit alpha-only' code to represent the primary key. — mvanolden, Feb 25 '19 at 16:39
@mvanolden that would still be a bad key. The key should remain a proper key. What you ask should be a computed "display only" field that isn't used in relations. — Panagiotis Kanavos, Feb 25 '19 at 16:56
Thanks everyone for your help! @LukStorms was able to provide the answer below. — mvanolden, Feb 26 '19 at 15:22

Gordon Linoff · Answer 1 · 2019-02-25T16:48:28.627

1

Have you considered convert()?

alter table t as pk_string as (convert(varchar(255), pk_column));

This adds a new computed column called pk_string which is the string representation of pk_column.

edited Feb 25 '19 at 16:48

answered Feb 25 '19 at 16:06

Gordon Linoff

1,242,037
58
646
786

Thank you for your attempt Gordon. However, this solution only converts the number into a string. I am looking to have the primary key (number) converted into alpha characters. That is what the PHP script in my original question is doing. Some output from the script above: 0 returns AAAAAB 1 returns AAAAAB 167854565 returns "NCHENV" I need this same output, but in SQL Server code. Also, it must be persistent (a stored value) so that I can search on it. Thank you again for your attempt. – mvanolden Feb 25 '19 at 16:46

LukStorms · Accepted Answer · 2019-02-26T08:04:49.143

0

You can add a User Defined Function for this

create /*or alter*/ function fnIntToPaddedBase26(@i int) 
returns varchar(7) 
as
begin
  declare @out varchar(7) = '';

  if @i = 0
    set @out = 'A';
  else
    set @i += 1;

  while @i > 0
    select @i -= 1, @out = char(ascii('A') + @i%26) + @out, @i/=26;

  if len(@out)<6
    set @out = right(replicate('A',6)+@out,6);

  return @out;
  end;
GO

Then use the UDF

select dbo.fnIntToPaddedBase26(167854565) as int2char;

returns:

NCHENV

edited Feb 26 '19 at 08:04

answered Feb 25 '19 at 16:51

LukStorms

28,916
5
31
45

You nailed it @LukStorms! The only edit I made was to change the lowercase 'a' to uppercase. Thank you so very much!! – mvanolden Feb 25 '19 at 23:05
a follow up question @LukStorms... How would I use this to create a persistent computed column? Can you please show me what the formula would be? Something like: dbo.fnIntToPaddedBase26(uniqueidhere) ? Would it be better if I created a trigger instead? – mvanolden Feb 25 '19 at 23:06
Not sure if it can be used as a persisted. Because I'm guessing that ms sql would complain that it's not deterministic. But something like `ALTER TABLE TableName ADD charcol AS dbo.fnIntToPaddedBase26(intcol);` for non-persisted. [Reference](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-computed-column-definition-transact-sql) And I've changed it back to uppercase 'A'. Because 'AA' & 'AAA' are both 0, I think. Btw, maybe you could make the UDF deterministic. See [here](https://stackoverflow.com/questions/3651662/). But I never tried that. – LukStorms Feb 26 '19 at 08:46
Thanks again @LukStorms! – mvanolden Feb 26 '19 at 15:21
@mvanolden You're welcome. Btw, the `varchar(7)` is because above 321272405 you get 7 letters. And weird enough, 321272406 returns 'AAAAAAA'. – LukStorms Feb 26 '19 at 15:52

SQL Server Computed Column - primary key to alpha characters

The PHP code:

2 Answers2