Converting Alphanumeric strings to a unique integer in MS-SQL

[cagey7696]

Is there a way to convert an alphanumeric string in a vchar field to a unique integer value. The string could contain alpha,numbers,'.','-','_',etc..... ????

 

[SQLSister]

And how would you determine what the number should be based on the existing data? Give us a sample of the existing values and the values you would like them converted to.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[cagey7696]

It doesn't matter what the integer value is -- it just needs to be unique and the algorithm shoulod always translate the same string to the same integer value..

 

[SQLSister]

Why not just add a GUI field then?

I don;t understand what you nmena when you say it must always translate the same string to the same integer at the same time you say every value must be uniwue. This is a contradiction in terms.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[cagey7696]

For example I have a string of "4474_SHEET-013-EL_116A", I would like to translate this to a pure integer value.

 

[dbomrrsm]

select checksum('4474_SHEET-013-EL_116A')

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

if the same value is passed again it will equate to the same checksum.

[blue]DBomrrsm[/blue]

 

[donutman]

Good answer DBomrrsm except he says he wants it to be unique, so you need to use a function that converts the ascii values of each letter, i.e. the hex value of the entire string! Or zip the string and take its hex value.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[cagey7696]

Yes - donutman, no other different string can convert to that same integer... CAn you suggest function ???

 

[dbomrrsm]

Good point ! but

shoulod always translate the same string to the same integer value

[blue]DBomrrsm[/blue]

 

[cagey7696]

Yes - I should have been more specific.... Would appreciate any help you can give me with code/function...

 

[dbomrrsm]

so do you need the function to convert the passed string to the same integer all the time or if it is passed again does it have to generate a unique integer - so different from the last time it was passed.

[blue]DBomrrsm[/blue]

 

[cagey7696]

it should generate the same integer all the time. I.e. the string would always convert to the same integer. AND a different string would never convert to that integer.

 

[dbomrrsm]

just read checksum cant guarantee uniqueness so my solution out of the window !!!!

[blue]DBomrrsm[/blue]

 

[donutman]

Ahh, I was being a bit facetious. How long can the string be? Big integers are 8 bytes, so you'd only be able to convert 8-byte strings unless you did some really esoteric coding to eliminate unneeded ascii values.
Then of course you could go really crazy and build your own big-big integers. But the real question is what are you really trying to do with this?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[cagey7696]

the string can be up to 25 characters long

 

[SQLSister]

I'm thinking the most straightforward way to do this is with a lookup table that contains the possible strings and an identity field. When a new string is added to the main table, you could have an insert/update trigger which adds iot to the lookup table before looking up the value to store in the table. The trigger would also use the lookup table to convert records where the existing string was alread in it.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[cagey7696]

we are really trying to create an interger key equilvalent into a legacy system that for some wierd reason only accepts integers in a field that is deeply entrenched in numerous applications.

 

[dbomrrsm]

this works:

create table checkit (val int)
insert into checkit (val)
values (checksum('this is about 25 charachters long'))
select * from checkit

[blue]DBomrrsm[/blue]

 

[donutman]

SQLSister to the rescue...the St. Bernard of TT.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]