SQL unieke key met cijfers en letters

[Urk]

Zeer gewaardeerde mede tweakers:

Ik wil in SQL Server 2000 een veld waarin ik naast een Primary Identifier (autonummering) een veld met een bepaalde unieke code met cijfers en letters, dit mag opeenvolgend zijn

Bijv
AHGR103
AHGR104
.
.
.
AHGR999
AHGS001

Of iets in die geest (alle codes moeten wel dezelfde lengte hebben, dus bijv. 5 karakters)
Is dit mogelijk om dit direct in SQL te regelen, na het automatisch toevoegen van een nieuw records via bijv VBScript?

[whoami]

Hmmm..... Ik denk dat je zelf zo'n id zult moeten gaan genereren mbhv een trigger.

[Urk]

Ik zie dit namelijk wel regelmatig bij databases. Weet alleen niet hoe het werkt.
Kijk bijv maar eens bij MSN Dating, daar krijgt ieder member een soort code in de vorm van MNO007 ofzo...

[Verwijderd]

Misschien bestaat het uit 2 delen

MNO (code)

en

007 -> 7 (autonummering)

wat vervolgens tot 1 id wordt samengevoegd

[Urk]

Ja ok, dat zou ook kunnen, maar hoe werkt dat dan dat als de letterreeks op is dat hij naar de volgende letter spring?
dus stel:

AA01
AA99
AB00
AB01

Die A moet dan over naar een B

[Morrar]

Ehm hex gebruiken als dat kan? Heb je cijfers en een beperkte range letters. Misschien niet helemaal wat je wilt, maja. Verder ken ik SQL Server 2000 helemaal niet, dus hoe je het kan fixen zou ik niet weten.

[Rataplan]

Morrar schreef op 22 April 2003 @ 00:47:
Ehm hex gebruiken als dat kan? Heb je cijfers en een beperkte range letters. Misschien niet helemaal wat je wilt, maja.

Dit is nog niet eens zo'n slecht antwoord - alleen moet je geen hexadecimale representatie opslaan (dat zou idd zwaar onvoldoende zijn), maar gewoon een decimaal nummer gebruiken, en dat pas bij weergave naar een alfanumerieke notatie omrekenen. We hebben het dan, denk ik, over een combinatie van het decimale met het dodecasesimale (klemtoon op aanvraag beschikbaar ) stelsel.

Hoe? In het voorbeeld heb je vier letters en drie cijfers, AHGR999. Dat zou je opslaan (en deze bewerking voer je uit als iemand een veldcode intikt en jij wil de sleutel waar je op moet zoeken hebben) als 999 + 1000 * (R + G * 26 + H * 26^2 + A * 26^3) waarbij je de letters vervangt door de positie in het alfabet, in dit geval respectievelijk 18, 7, 8 en 1): 23184999, krijg je dan.

Terugrekenen? Alle delingen zijn integer-delingen, dus 3,999 = 3:

23184999 / 1000: 24183, rest 999;
23184 / 26 = 891; rest 18;
891 / 26 = 34; rest 7;
34 / 26 = 1; rest 8;
en 1 / 26 is natuurlijk 0 rest 1.

Probeer nu zelf wat je krijgt als je 23185000 probeert te converteren

Overigens moet je dit natuurlijk, afhankelijk van je project, in een stored procedure opslaan, aan een trigger hangen, allebei, of uit de aanroepende code modulariseren. Maar ik neem aan dat dat het probleem niet is

[Urk]

Rataplan schreef op 22 April 2003 @ 01:32:
[...]

Dit is nog niet eens zo'n slecht antwoord - alleen moet je geen hexadecimale representatie opslaan (dat zou idd zwaar onvoldoende zijn), maar gewoon een decimaal nummer gebruiken, en dat pas bij weergave naar een alfanumerieke notatie omrekenen. We hebben het dan, denk ik, over een combinatie van het decimale met het dodecasesimale (klemtoon op aanvraag beschikbaar ) stelsel.

Hoe? In het voorbeeld heb je vier letters en drie cijfers, AHGR999. Dat zou je opslaan (en deze bewerking voer je uit als iemand een veldcode intikt en jij wil de sleutel waar je op moet zoeken hebben) als 999 + 1000 * (R + G * 26 + H * 26^2 + A * 26^3) waarbij je de letters vervangt door de positie in het alfabet, in dit geval respectievelijk 18, 7, 8 en 1): 23184999, krijg je dan.

Terugrekenen? Alle delingen zijn integer-delingen, dus 3,999 = 3:

23184999 / 1000: 24183, rest 999;
23184 / 26 = 891; rest 18;
891 / 26 = 34; rest 7;
34 / 26 = 1; rest 8;
en 1 / 26 is natuurlijk 0 rest 1.

Probeer nu zelf wat je krijgt als je 23185000 probeert te converteren

Overigens moet je dit natuurlijk, afhankelijk van je project, in een stored procedure opslaan, aan een trigger hangen, allebei, of uit de aanroepende code modulariseren. Maar ik neem aan dat dat het probleem niet is

Jeetje, hmmmm...kan je het iets makkelijker uitleggen?
Om kort te zijn wil je dus letters converteren naar cijfers?

Er is trouwens toch ook een ASP functie waarmee je cijfers in een soort code converteert of andersom?

[RobIII]

Je kunt ook een GUID gebruiken, die zien er als volgt uit:

{115F71A2-6976-4620-AE3D-B3B8F174CE36}

Maar die zijn misschien een beetje te lang? Zet je kolom type op Unique Identifier en de default value op "NewID()" (zonder de quotes). SQL genereert dan voor ieder record een nieuw GUID. Je kunt deze dan ook zelf laten oplopen als je wilt, maar daar komt dan weer een trigger bij kijken ofzo...

[ Voor 52% gewijzigd door RobIII op 22-04-2003 11:26 ]

[Rataplan]

Urk schreef op 22 april 2003 @ 11:18:
[...]

Jeetje, hmmmm...kan je het iets makkelijker uitleggen?
Om kort te zijn wil je dus letters converteren naar cijfers?

Ja. Je kan gewoon je autogenummerde ID weergeven alsof er letters inzitten. Dit is net zoiets als binair of hexadecimaal rekenen, als je dat niet beheerst wordt het een heeel lang verhaal.

Een fout heb ik al gemaakt: voor het 26-tallig stelsel, zoals ik dat hanteer, moet je de letters de waarden A=0...Z=25 geven, en niet 1 hoger, dan krijg je grote problemen

Maar het voorbeeld spreekt voor zich en werkt gegarandeerd. Kan je aangeven wat je precies niet volgt, dan kan ik daar wel wat meer over proberen te vertellen...

[ Voor 7% gewijzigd door Rataplan op 22-04-2003 14:08 ]

[Verwijderd]

in sql server moet je gewoon een functie aanmaken die zo'n code voor je maakt, bijv:

'ABC' + MAX(tabel_naam.tabel_kolom)

Deze functie stel je dan als default waarde in...

Een functie, dus geen trigger

[ Voor 14% gewijzigd door Verwijderd op 22-04-2003 13:55 ]

[Morrar]

De Rataplan oplossing lijkt mij ook de goede. Tijz oplossing werkt niet met nummers die oplopen. Dus genereer een auto-incremented integer en zet die om in alfanumeriek. De functie daarvoor is niet zo heel moeilijk. Ik heb er ff 1 geknutseld in Javascript (had niks anders debugbaars bij de hand dus). Je kunt zelf aangeven hoe lang de alfanumerieke uitvoer moet zijn. Ook kun je bepalen welke tekens mee mogen doen aan de hand van de sConvChars-string. Je moet dit Javascriptje zelf nog ff porten naar de SQL server 2000 omdat ik, zoals ik al zei, deze niet ken. Dan moet die string met conversie-chars misschien een array worden maar het idee is hetzelfde.

Het scriptje:

JavaScript:

<html>
<head>
<script language="javascript">
//decimal 2 alphanumeric conversion
//iInput is the integer to be converted
//iCLength is the minimum length of the alphanumeric string
function Dec2ANum(iInput, iCLength)
{   //define conversion variables
    var sConvChars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    var iConvStates = sConvChars.length;
    
    var sAnumStr = "";

    //calculate alpha-numeric string
    for(var iPower = 1; iInput > 0; iPower++)
    {   var iRes = iInput % Math.pow(iConvStates, iPower);
        iInput -= iRes;
        iRes = iRes / Math.pow(iConvStates, iPower - 1);
        sAnumStr = sConvChars.charAt(iRes) + sAnumStr;
    }

    //padding
    if(sAnumStr.length < iCLength)
    {   for(var i = sAnumStr.length; i < iCLength; i++) { sAnumStr = '0' + sAnumStr; }
    }   
    return sAnumStr;
}

function FormSubmit(oForm)
{   var iInput = parseInt(oForm.IntInput.value);
    var sOutput = Dec2ANum(iInput, 6);
    self.alert(sOutput);
}
</script>
</head>
<body>
<form>
Integer: <input type="text" name="IntInput">
<input type="button" value="convert" onclick="FormSubmit(this.form);">
</form>
</body>
</html>

//edit:
ff layout-verneuking verholpen

[ Voor 16% gewijzigd door Morrar op 22-04-2003 15:20 ]

[Rataplan]

Morrar schreef op 22 april 2003 @ 15:17:
(stukje code)

Fraai! Hebben we de implementatie ook al bij de hand

Een opmerking, wel: deze code gebruikt zowel numerieke als alfanumerieke tekens voor een zelfde positie, dus de opvolger van AHGR999 is dan AHGR99A. TS zal dus zelf moeten bepalen welk gedeelte uitsluitend numeriek is, en welk gedeelte hij op basis van dit algoritme naar letters moet converteren.

Maar dat is het minste van de problemen, lijkt mij.

Overigens beschik ook ik niet over de benodigde SQLServer-kennis om dit te vertalen...

* Rataplan kijkt eens vragend om zich heen - iemand?

[ Voor 13% gewijzigd door Rataplan op 22-04-2003 16:06 ]

[Morrar]

Rataplan heeft gelijk dat dit scriptje idd de hele zooi in een '36-tallig' stelsel omzet. Als je wilt dat een deel letters wordt en een ander deel numeriek dan moet je ff het scriptje aanpassen. Ik heb wederom ff mijn Javascriptje aangepast en volgens mij werkt het zo, maar heb nog geen tijd gehad het grondig te checken. De sConvChars heb ik ff een array van gemaakt en daar staan nu alleen letters in. Je zou er trouwens ook lettergrepen, smilies of wat dan ook in kunnen doen. De code:

JavaScript:

//decimal 2 alphanumeric conversion
//iInput is the integer to be convert
//iCLength is the minimum length of the alphanumeric string
//iNLength is the number of characters at the end that should stay numeric
function Dec2ANum(iInput, iCLength, iNLength)
{   //define conversion variables
    var aConvChars = new Array('A','B','C','D','E','F','G','H','I','J', 'K','L','M','N','O','P',
                               'Q','R','S','T','U','V','W','X','Y','Z');
    var iConvStates = aConvChars.length;
    
    var sAnumStr = "";

    //Split in two parts and only convert the one after iNLength
    var sSuffix = iInput % Math.pow(10, iNLength) + ""; //force string
    var iConvert = Math.floor(iInput / Math.pow(10, iNLength));
    
    //calculate alpha-numeric string
    for(var iPower = 1; iConvert > 0; iPower++)
    {   var iRes = iConvert % Math.pow(iConvStates, iPower);
        iConvert -= iRes;
        iRes = iRes / Math.pow(iConvStates, iPower - 1);
        sAnumStr = aConvChars[iRes] + sAnumStr;
    }
    
    //padding
    if(sAnumStr.length < iCLength - iNLength)
    {   for(var i = sAnumStr.length; i < iCLength - iNLength; i++)
        { sAnumStr = aConvChars[0] + sAnumStr; }
    }

    if(sSuffix.length < iNLength)
    {   for(var i = sSuffix.length; i < iNLength; i++)
        { sSuffix = '0' + sSuffix; }
    }

    return sAnumStr + sSuffix;
}

De Array() moet overigens op 1 regel, maar dat was niet zo handig ivm layout.
Je moet trouwens ook de aanroep even wijzigen:

JavaScript:

var sOutput = Dec2ANum(iInput, 6, 3);

[ Voor 52% gewijzigd door Morrar op 22-04-2003 18:32 ]

[Urk]

OK, Morrar en Rataplan, bedankt voor de hulp
Ik ga een deze dagen het scriptje eens goed doorlezen en proberen te porteren naar VBScript (ik gebruik het immers in ASP) Wat ik daarna gewoon in SQL kan pleuren natuurlijk

Tnx, I'll keep you up to date...

[Annie]

Urk schreef op 23 April 2003 @ 23:51:
Ik ga een deze dagen het scriptje eens goed doorlezen en proberen te porteren naar VBScript (ik gebruik het immers in ASP)

Het één (asp) is natuurlijk niet onlosmakelijk verbonden met het ander (vbscript). Je kan in je ASP script ook gebruik maken van deze (jscript) code.
Het omschrijven naar vbscript zou overigens niet gek veel tijd moeten kosten.